Thread: Stop a Macro
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default 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.....