+ Reply to Thread
Results 1 to 10 of 10

Userform cancel button - how to exit multiple subs

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Userform cancel button - how to exit multiple subs

    Hello,

    I have a spreadsheet that generates a report in Word depending on what users select in a userform.

    The architecture is like this:

    master macro - runs/calls all the macros below

    macro 1 - displays the userform
    macro 2 - does stuff based on the userform input
    macro 3 - does more stuff
    ....
    macro 8 - generates report


    The problem I am having is with the cancel button (the red x in the top corner). If I click on the cancel button, the userform disappears, then macro 2 runs,
    then macro 3 runs
    then macro 4 runs
    all the way until the last macro.

    Is there anyway to stop ALL the macros running, if the user presses cancel on the userform?

    This is really driving me crazy. I don't want to add a "Cancel" command button, or hide the red x, or reassign it. I just need some code to make it stop all macros.

    Any help greatly appreciated...

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Userform cancel button - how to exit multiple subs

    Try just using End.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform cancel button - how to exit multiple subs

    Don't just use End. Ever.
    You should trap the X button using the QueryClose event of the form and trigger your own Cancel routine as a result of it. Do you already have a Cancel button?
    Remember what the dormouse said
    Feed your head

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Userform cancel button - how to exit multiple subs

    Quote Originally Posted by romperstomper View Post
    Don't just use End. Ever.
    Aye, you're right that was a very poor suggestion.

    I shall go and berate myself for suggesting such a sloppy piece of coding

    Dom

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform cancel button - how to exit multiple subs

    It is effective, to be fair - just a little OTT.

  6. #6
    Registered User
    Join Date
    05-26-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Userform cancel button - how to exit multiple subs

    Quote Originally Posted by romperstomper View Post
    Don't just use End. Ever.
    You should trap the X button using the QueryClose event of the form and trigger your own Cancel routine as a result of it. Do you already have a Cancel button?

    Hi guys, thanks for the suggestions. Romperstomper, what is the syntax for QueryClose?

    I am assuming you mean in the master macro I put something like -

    if the userform was closed with the cancel button, then don't do anything
    else
    run the rest of the macros


    Is that right? Do I need to add anything to the userform code?

    Really sorry for my ignorance, I've been using vba for a while but have only recently started using userforms.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform cancel button - how to exit multiple subs

    No - the QueryClose code goes in the userform.
    Typically what you need is a Public boolean variable (e.g. blnCancelled) declared in the form's code module that is set to True if the user presses a Cancel button or the X to close the form. The form needs to be hidden, not unloaded, no matter whether you press Cancel, OK or X to close it. The folowing code then checks the blnCancelled variable and, if it's False, exits; if not, then it carries on with the rest of the code.

    Essentially, at the top of the form, you have:
    Please Login or Register  to view this content.
    then your OK (or Submit or whatever) button just uses code like:
    Please Login or Register  to view this content.
    and you have a Cancel button:
    Please Login or Register  to view this content.
    and then QueryClose code:
    Please Login or Register  to view this content.
    Finally, your calling code looks something like this:
    Please Login or Register  to view this content.
    Does that help?

  8. #8
    Registered User
    Join Date
    05-26-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Userform cancel button - how to exit multiple subs

    That's excellent, mate. Cheers I'll give it a go now.
    What is the syntax for running this from the red x button in the top corner, rather than through a "cancel" command button I place on the userform?
    Any idea? If not, I'll go with what you have written...

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform cancel button - how to exit multiple subs

    If you don't want a Cancel button at all (looks better if you have one, IMO), remove the cmdCancel_Click code and alter the Queryclose code to:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-26-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Userform cancel button - how to exit multiple subs

    Hey mate

    I got it working (using your original code) - sorry, I didn't realize the original code was referencing the red x button.

    I too, much prefer it with a working red x button.

    You sir, are a legend.

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1