VBA help - error message to user

[haas786]

Hi all! I need help with programming a macro. Currently, I have a
macro which when I run it, grabs data from certain cells and copies
and pastes it in another worksheet. That is working well, so no
problems there. However, I need to add another element to this macro.

In cell B2, I have a drop down list containing 4 values: "C", "X",
"A", and "T". If the user selects "X", then (s)he must also fill in
cells E1, E2, and E3 with pertinent info. If the macro is run, and
those cells are blank (given that B2 is "X"), I want an error message
to pop up saying "Can't export data unless cells E1, E2, and E3 are
filled in."
This would essentially halt the macro and the user has to fill in the
data, and re-run again. If any other value (other than "X") is chosen
in cell B2, the macro runs successfully.

Can anyone please help?

Thank you all in advance!

 

[Guest]

How about something like this:

Dim Match as boolean
DIm i as long

Match = TRUE

for i = 1 to 3
if isempty(cells(i,"E")) then
MATCH = FALSE
exit for
end if
next i

if not MATCH then
msgbox("Can't export data unless cells E1, E2, and E3 are filled in.")
end if

HTH,
Barb Reinhardt

 

[merjet]

Sub Macro1() ' or whatever name it is
Dim bTest As Boolean
bTest = FailTest
If bTest = True Then Exit Sub
' rest of code
End Sub

Private Function FailTest() As Boolean
If Range("B2") = "X" Then
If IsEmpty(Range("E1")) Or IsEmpty(Range("E2")) Or _
IsEmpty(Range("E3")) Then
MsgBox "Can't export data unless cells E1, E2, and E3 are
filled in."
FailTest = True
Else
FailTest = False
End If
End If
End Function

Hth,
Merjet

 

[haas786]

Sub Macro1() ' or whatever name it is
Dim bTest As Boolean
bTest = FailTest
If bTest = True Then Exit Sub
' rest of code
End Sub

Private Function FailTest() As Boolean
If Range("B2") = "X" Then
If IsEmpty(Range("E1")) Or IsEmpty(Range("E2")) Or _
IsEmpty(Range("E3")) Then
MsgBox "Can't export data unless cells E1, E2, and E3 are
filled in."
FailTest = True
Else
FailTest = False
End If
End If
End Function

Hth,
Merjet

Thank you Merjet...this worked. You saved the day yet again!