Introduction
There are a number of (good) examples of how to cancel via forms, controls and msgbox(s). However, there is often a need to execute a cancel that carries up through calling subs. Here is an example of 2 subs that do just that. The example is writen in a module in 2003 Excel VBA.
Details
First a global variable bolCancel is declared in the Declaration Section. Then the main sub is executed, and the bolCancel is initialized with a "no" cancel condition.
Declaration Section
The main sub topSub is intended to show that it executes some code. A msgbox is used to simulate some code for the basis of the decision to cancel or to proceed execution of code.
topSub
From the main sub topSub we go two ways.
On a "yes" cancel in topSub, there is no need to use bolCancel, just an Exit Sub is sufficient to abort.
A "no" cancel just allows the code to continue in topSub by calling someOtherSub.
someOtherSub
In the called SomeOtherSub we also have code to simulate a cancel/no-cancel conditions. However here, on a cancel request, the global bolCancel is set to True and the sub is exited/aborted.
When SomeOtherSub returns, topSub can check for a cancel request and act accordingly.
Conclusion
There are no doubt other ways of achieving the same purpose. The intent here is to show an example with more detail and possible methods for exiting/aborting. I have fond (no criticism intended) other examples often exclude details, as they are meant to be intuitively obvious. To me, "intuitivel y obvious" never is. Hope this helps some of you.
There are a number of (good) examples of how to cancel via forms, controls and msgbox(s). However, there is often a need to execute a cancel that carries up through calling subs. Here is an example of 2 subs that do just that. The example is writen in a module in 2003 Excel VBA.
Details
First a global variable bolCancel is declared in the Declaration Section. Then the main sub is executed, and the bolCancel is initialized with a "no" cancel condition.
Declaration Section
Code:
Option Explicit 'declare bolCancel as a global variable Public bolCancel As Boolean
topSub
Code:
Public Sub topSub()
Dim choice As Integer
'initialize Cancel, do this first
bolCancel = False
'could execute some code here
'to test without code
'msgbox is used to simulate Cancel/No-cancel
choice = MsgBox("1. topSub," & _
vbCr & "Click OK to test NO cancel in this topSub," & _
vbCr & "Click Cancel to test YES cancel", vbOKCancel)
'chech the cancel condition by some code above
Select Case choice 'choice could be created by any other code
Case 1 'ok (return code by msgbox)
'call some other sub
MsgBox "2. topSub," _
& vbCr & "this topSub has NO cancel condition," & _
vbCr & "and will call SomeOtherSub now", vbOKOnly
someOtherSub
Case 2 'Cancel (return code by msgbox)
'this topSub code has a cancel condition
MsgBox "3. topSub," & _
vbCr & "this topSub has a cancel condition," & _
vbCr & "and will cancel/exit now", vbOKOnly
Exit Sub
End Select
'check if Sub Call returned a Cancel
Select Case bolCancel
Case False 'No Cancel
'call some other sub
MsgBox "4. topSub," & _
vbCr & "SomeOtherSub did NOT return Cancel," & _
vbCr & "and is executed normally through here", vbOKOnly
'execution continues after "end select"
Case True 'Yes Cancel
'call some other sub
MsgBox "5. topSub," & _
vbCr & "SomeOtherSub did return Cancel," & _
vbCr & "and is aborted through here", vbOKOnly
'therefore we can also cancel the topSub, if necessary
Exit Sub
End Select
'remaining code here
MsgBox "7. topSub," & _
vbCr & "this topSub has executed normally," & _
vbCr & "and will exit now", vbOKOnly
End Sub
On a "yes" cancel in topSub, there is no need to use bolCancel, just an Exit Sub is sufficient to abort.
A "no" cancel just allows the code to continue in topSub by calling someOtherSub.
someOtherSub
Code:
Sub someOtherSub()
Dim response As Integer
'execute some code
'to test without code
'the message box simulates code that causes a condition,
'from where you want to Cancel the call to SomeOtherSub
response = MsgBox("8. SomeOtherSub," & _
vbCr & "Click OK to test NO cancel in this SomeOtherSub," & _
vbCr & "Click Cancel to test YES cancel", vbOKCancel)
'chech what was returned by the message box, or some other code above
Select Case response
Case 1 'ok
'execute some other code
'execution continues after "end select"
Case 2 'Cancel
'cancel and return to calling sub
MsgBox "9. SomeOtherSub," & _
vbCr & "this SomeOtherSub has a cancel condition," & _
vbCr & "and will cancel/exit now", vbOKOnly
bolCancel = True
Exit Sub
End Select
'other code here
MsgBox "10. SomeOtherSub," & _
vbCr & "after executing code," & _
vbCr & "this SomeOtherSub is returning normally", vbOKOnly
End Sub
When SomeOtherSub returns, topSub can check for a cancel request and act accordingly.
Conclusion
There are no doubt other ways of achieving the same purpose. The intent here is to show an example with more detail and possible methods for exiting/aborting. I have fond (no criticism intended) other examples often exclude details, as they are meant to be intuitively obvious. To me, "intuitivel y obvious" never is. Hope this helps some of you.