Exit Main Sub from a "Called" procedure: - Excel VBA

Dear Experts

I am running a sub that calls other procedures, rather than have one long main sub.
One of the subs called ends with a msgbox with vbOKCancel as its buttons.

vbOK is fine; that just continues the main procedure.

How do i get the main procedure to exit if the msgbox from the called routine is vbCancel?

Please see attached workbook example.
Query-ExitSub.xlsm
David PhelopsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
There are two ways to do this

The first way is easy but not good if global variables are used. Use this in the called sub

        mbr = MsgBox("An unknown error.....", vbOKCancel)
        If mbr = vbCancel Then End


The second way is to return the mbr variable to the main (calling) sub and then exit if mbr=vbCancel
0
 
Patrick MatthewsConnect With a Mentor Commented:
Generally, instead of making the subsidiary routines subs, make them functions that return a value to the calling sub that allows for a test of whether to continue.

For example, if function Foo returns a boolean...


    If Foo(SomeArgumentHere) = False Then
        MsgBox "Oh my, time to stop!", vbExclamation
        Exit Sub
    End If

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Example of the second method

Sub Main()
.
.
calledsub(mbr)
if mbr=vbCancel then exit sub
.
.
end sub


sub calledsub(mbr)
.
.
mbr=msgbox("message",vbOKCancel)
if mbr=vbCancel then exit sub
.
.
end sub
0
 
David PhelopsAuthor Commented:
@ssaqibh
Thank you - fortunately, I have no global variables in this routine, so method #1 is fine for now.
Method #2 i shall keep in reserve!
Cheers - I like simple, elegant solutions!

I should like to give @matthewspatrick some credit for suggesting the function route, which i'm sure with more time and application, i can implement
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.