Called subroutine within macro does not function except in Debug / F8 Step Into mode
Hello Experts,
In the following macro the five "CallClearPlan 1 to 5 routines when run do not clear the 5 plans and the macro ends properly. When I Debug using F8 the macro steps through each of the 5 called Subs and does clear all fields in all 5 plans.
How to correct this poor behavior is my question?
Sub ClearResponsesAndPlans()
'
'
'
Response = MsgBox(prompt:="Select 'Yes' if you want to clear all 5 plans 'No' to quit.", Buttons:=vbYesNo + vbInformation)
' If statement to check if the yes button was selected.
If Response = vbYes Then
MsgBox "You selected 'Yes' to clear each of 5 Plans", vbExclamation, "Clear Macro"
Call ClearPlan1
Call ClearPlan2
Call ClearPlan3
Call ClearPlan4
Call ClearPlan5
Else
' The no button was selected.
MsgBox "You selected 'No'. Press 'OK' to quit", vbCritical, " Quit Macro "
End If
Sheets("Assessment").Selec
ActiveWindow.SmallScroll Down:=-87
Range("Assess_Responses").
End Sub
~-~-~-~-~-~-~-~-~-
Below is a one of the five typical Called sub routines each of which function properly from individual commandbuttons...
~-~-~-~-~-~-~-~-~-
Sub ClearPlan1()
'
' ClearPlan1 Macro
'
'
Range( _
"B26:P30,B33:P37,D41:M43,D
).Select
Selection.ClearContents
Range("B8").Activate
End Sub
Thank you
In the following macro the five "CallClearPlan 1 to 5 routines when run do not clear the 5 plans and the macro ends properly. When I Debug using F8 the macro steps through each of the 5 called Subs and does clear all fields in all 5 plans.
How to correct this poor behavior is my question?
Sub ClearResponsesAndPlans()
'
'
'
Response = MsgBox(prompt:="Select 'Yes' if you want to clear all 5 plans 'No' to quit.", Buttons:=vbYesNo + vbInformation)
' If statement to check if the yes button was selected.
If Response = vbYes Then
MsgBox "You selected 'Yes' to clear each of 5 Plans", vbExclamation, "Clear Macro"
Call ClearPlan1
Call ClearPlan2
Call ClearPlan3
Call ClearPlan4
Call ClearPlan5
Else
' The no button was selected.
MsgBox "You selected 'No'. Press 'OK' to quit", vbCritical, " Quit Macro "
End If
Sheets("Assessment").Selec
ActiveWindow.SmallScroll Down:=-87
Range("Assess_Responses").
End Sub
~-~-~-~-~-~-~-~-~-
Below is a one of the five typical Called sub routines each of which function properly from individual commandbuttons...
~-~-~-~-~-~-~-~-~-
Sub ClearPlan1()
'
' ClearPlan1 Macro
'
'
Range( _
"B26:P30,B33:P37,D41:M43,D
).Select
Selection.ClearContents
Range("B8").Activate
End Sub
Thank you
Which sheet are the ranges you want to clear on?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
all of the plans are on one sheet: "Action Plans"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes they do when I invoke each one using 5 separate command buttons on the same sheet.
Sorry I don't follow.
If you change the code for each sub to include the sheet reference, as in the example I posted, then the code should work wherever/however it's called.
In the original code there was no sheet reference so the code would be executed on whichever sheet was active at the tim.
If you change the code for each sub to include the sheet reference, as in the example I posted, then the code should work wherever/however it's called.
In the original code there was no sheet reference so the code would be executed on whichever sheet was active at the tim.
ASKER
I awarded both of you points because both of your questions arrived just two minutes apart led me to the answer that had escaped me. By adding a Sheets"Select "Action Plans".select statement it now works fine when called from another sheet.
Thanks for your guidance.
Berry
Thanks for your guidance.
Berry
Berry
There's no need to Select/Activate and doing so can cause problems, eg subsequent code running on wrong sheet.
You can just add the sheet reference as I did in the code I posted.
There's no need to Select/Activate and doing so can cause problems, eg subsequent code running on wrong sheet.
You can just add the sheet reference as I did in the code I posted.
ASKER
Thank you for again clearing up my error
Berry
Berry