Stop a Macro
 

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.....

Stop a Macro
 

The only reason I can see that it wouldn't stop is if the value in the cell
is or = to 7. In otherwords, you aren't executing the Exit Sub command.
When you execute the Exit Sub command, the sub is terminated. Could the
cell have text in it instead of a number?

Other problems may be that you are calling the sub from another sub - Exit
sub does not stop code execution except for that sub. But the specific
example of Monday being less than 7 and Tuesday executing should not happen
it the cell contains a number < 7

--
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.....

Stop a Macro
 

Tom, what some of the cells have is error values i.e. #Values etc


"Tom Ogilvy" wrote in message
...
The only reason I can see that it wouldn't stop is if the value in the
cell
is or = to 7. In otherwords, you aren't executing the Exit Sub command.
When you execute the Exit Sub command, the sub is terminated. Could the
cell have text in it instead of a number?

Other problems may be that you are calling the sub from another sub - Exit
sub does not stop code execution except for that sub. But the specific
example of Monday being less than 7 and Tuesday executing should not
happen
it the cell contains a number < 7

--
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.....

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.....

Stop a Macro
 

Thanks Tom, a neat way you suggest to shorten my macro's


"Tom Ogilvy" wrote in message
...
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.....