Results 1 to 8 of 8

Cancel button on userform

This is a discussion on Cancel button on userform within the Excel Questions forums, part of the Question Forums category; I've got my form and macros working now (thanks to Richie for his help). My only problem now is that ...

  1. #1
    New Member
    Join Date
    Mar 2003
    Posts
    23

    Default Cancel button on userform

    I've got my form and macros working now (thanks to Richie for his help).

    My only problem now is that when you click on the cancel button on the form it still runs the macro before it closes the form. Where do I need to put the code for the cancel command button? Right now it's under forms but the code that calls up the form is in a macro in a module and I have a button on the spreadsheet that runs the macro and user form.

  2. #2
    pcc
    pcc is offline
    Board Regular pcc's Avatar
    Join Date
    Jan 2003
    Location
    Derby, UK
    Posts
    1,326

    Default Re: Cancel button on userform

    Probably need

    Code:
    Unload Me
    End
    Using Windows 2000/ Excel 2000/ Access 97 sometimes, or
    Windows Vista/ Excel 2007/Access 2007 sometimes....

  3. #3
    New Member
    Join Date
    Mar 2003
    Posts
    23

    Default Re: Cancel button on userform

    OK, another stupid question:

    how do I check if the cancel button was clicked?

    Can I add an if statement like
    Code:
    If CommandButton2.Value = True Then
        Unload UserForm1
        GoTo Cancel
        Else
    .....
    ??

    I'm sorry if these are basic questions but I'm feeling stumped and stupid. I create these forms once every couple of years.

  4. #4
    pcc
    pcc is offline
    Board Regular pcc's Avatar
    Join Date
    Jan 2003
    Location
    Derby, UK
    Posts
    1,326

    Default Re: Cancel button on userform

    If it's clicked the code will run. If it's not clicked, it won't. Therefore you don't have to 'check' if it was clicked per se, the code will do it for you.
    Private Sub CommandButton1_Click()
    unload me
    end
    End Sub
    Using Windows 2000/ Excel 2000/ Access 97 sometimes, or
    Windows Vista/ Excel 2007/Access 2007 sometimes....

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Location
    Romania/Netherlands
    Posts
    2,502

    Default Re: Cancel button on userform

    I have something similar.
    My form is called from a macro, then if the form is closed (either by pressing OK or Cancel) some button dependent code is processed.

    then in the macro that called the form I want to differentiate what happens next depending on which button was pressed. How can I tell?

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,101

    Default Re: Cancel button on userform

    Add a public variable to the form module, something like:
    Code:
    Public blnCancel as Boolean
    then in your OK button, you use:
    Code:
    Private sub cmdOK_Click()
       blnCancel = False
       Me.Hide
    End Sub
    and your Cancel button is:
    Code:
    Private sub cmdOK_Click()
       blnCancel = True
       Me.Hide
    End Sub
    then your calling code looks like:
    Code:
    Sub DoStuff()
    Userform1.Show
    If Userform1.blnCancel then
       ' cancel button was pressed
    Else
       ' OK button was pressed
    End If
    Unload Userform1
    adjusting the names as required.

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    Romania/Netherlands
    Posts
    2,502

    Default Re: Cancel button on userform

    Thanks rorya! I could have dreamt up this myself of course. Kindof stopped using public variables.

    Great tag line by the way.

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,101

    Default Re: Cancel button on userform

    Well, yes, in the real world you should have a Property declared that reads and writes the private variable, and you wouldn't use Userform1.Show you would use a variable instead, but I was feeling lazy!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com