Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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
    Austria/Netherlands
    Posts
    2,885

    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
    25,292

    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
    Austria/Netherlands
    Posts
    2,885

    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
    25,292

    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!

  9. #9
    New Member
    Join Date
    Apr 2014
    Posts
    5

    Default Re: Cancel button on userform

    Hi, I have a somewhat similar issue. I apologize in advance if my terminology or explanation is lacking but I am so very new to VBA. I created a macro that creates a directory which is named from a variable that is entered buy the user in an input box. The OK and Cancel buttons are already available however, when the cancel button is selected, the macro continues to run and creates a directory but it names it "False". Here is my code:


    'Create a directory
    Sub CreateNewDir()
    Dim x As String
    ChDir "c:\Sales Engineering\Cash Flow Projections"
    If x = vbNullString Then
    Exit Sub
    End If
    x = Application.InputBox("Enter number for folder you wish to create.", "CF Number", "CF15-", , , , 1)
    MkDir "c:\Sales Engineering\Cash Flow Projections\" & x
    ChDir "c:\Sales Engineering\Cash Flow Projections"
    ChDir "c:\Sales Engineering\Cash Flow Projections\" & x

    End Sub


    What am I doing wrong? I would be very grateful for any help.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    21,527

    Default Re: Cancel button on userform

    Quote Originally Posted by ataloss View Post
    Hi, I have a somewhat similar issue. I apologize in advance if my terminology or explanation is lacking but I am so very new to VBA. I created a macro that creates a directory which is named from a variable that is entered buy the user in an input box. The OK and Cancel buttons are already available however, when the cancel button is selected, the macro continues to run and creates a directory but it names it "False". Here is my code:

    Code:
    'Create a directory
    Sub CreateNewDir()
      Dim x As String
      ChDir "c:\Sales Engineering\Cash Flow Projections"
      If x = vbNullString Then
        Exit Sub
      End If
      x = Application.InputBox("Enter number for folder you wish to create.", "CF Number", "CF15-", , , , 1)
      If x <> "False" Then
        MkDir "c:\Sales Engineering\Cash Flow Projections\" & x
        ChDir "c:\Sales Engineering\Cash Flow Projections"
        ChDir "c:\Sales Engineering\Cash Flow Projections\" & x
      End If
    End Sub
    What am I doing wrong? I would be very grateful for any help.
    First off, the InputBox buttons do not take action, all that happens is a code is returned a value that you, the programmer, can react to. In the case of the Application.InputBox, if Cancel is click, it returns the value False, otherwise it returns whatever was in the InputBox's input field. So, you need to add the green highlighted lines of code above in order to make your Cancel button meaningful. However, you will still have a problem due to the lines of code I highlighted in red... since x does not have any value at that point of the code, the Sub will be exited immediately before any code gets to run. Why is that code in there? I think you can just remove them along with adding the green lines of code.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

Page 1 of 2 12 LastLast

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