Stop a Macro
Sub Generate_Reports()
Dim v(1 to 3) as String
Dim bExit as Boolean
v(1) = "Monday"
v(2) = "Tuesday"
v(3) = "Wednesday"
Sheets("Figures").Select
With Worksheets("Figures")
bExit = False
for i = 1 to 3
set rng = .Range("AM2").Offset(i-1,0)
if iserror(rng) then
bExit = True
Elseif not isnumeric(rng) then
bExit = True
if rng.Value < 7 then
bExit = true
end if
if bExit then
msgbox "You have not explained why your Cash Variance for " & _
v(i) & "shows a high variance, you cannot e-mail until you do so"
Exit sub
end if
Next i
End With
<Rest of Macro.....
or
(but in some versions of excel this might raise an error for some
comparisons)
Sub Generate_Reports()
Dim v(1 to 3) as String
v(1) = "Monday"
v(2) = "Tuesday"
v(3) = "Wednesday"
Sheets("Figures").Select
With Worksheets("Figures")
for i = 1 to 3
set rng = .Range("AM2").Offset(i-1,0)
if not(rng.Value = 7) then
msgbox "You have not explained why your Cash Variance for " & _
v(i) & "shows a high variance, you cannot e-mail until you do
so"
Exit sub
end if
Next i
End With
<Rest of Macro.....
--
Regards,
Tom Ogilvy
"John" wrote in message
...
I have the following code which display a Message box if certain
conditions
are not met. What I wish is that it should only show the first message and
not process any of the re-maiing part of the macro. It should just stop,
but
it doesn't. So as below, it sholdn't execute "Tuesday" if "Monday" is
displayed
Thanks
Sub Generate_Reports()
Sheets("Figures").Select
With Worksheets("Figures")
If .Range("AM2").Value < 7 Then
MsgBox "You have not explained why your Cash Variance for Monday
shows
a high variance, you cannot e-mail until you do so"
Exit Sub
End If
End With
With Worksheets("Figures")
If .Range("AM3").Value < 7 Then
MsgBox "You have not explained why your Cash Variance for Tuesday
shows a high variance, you cannot e-mail until you do so"
Exit Sub
End If
End With
With Worksheets("Figures")
If .Range("AM4").Value < 7 Then
MsgBox "You have not explained why your Cash Variance for Wednesday
shows a high variance, you cannot e-mail until you do so"
Exit Sub
End If
End With
<Rest of Macro.....
|