Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MSG Box - Stop Macro | Excel Programming | |||
How do I stop a macro from running within a macro? | Excel Programming | |||
Stop running a macro in the middle of a macro | Excel Programming | |||
Macro: With Stop it works. Without Stop it doesn't. | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |