Cancel button on userform

KCIWebMaster

New Member
Joined
Mar 5, 2003
Messages
23
I've got my form and macros working now (thanks to Richie for his help). :pray:

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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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. :oops:
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Thanks rorya! I could have dreamt up this myself of course. Kindof stopped using public variables.

Great tag line by the way.
 
Upvote 0
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! ;)
 
Upvote 0
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.
 
Upvote 0
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"
[COLOR="#FF0000"][B]  If x = vbNullString Then
    Exit Sub
  End If[/B][/COLOR]
  x = Application.InputBox("Enter number for folder you wish to create.", "CF Number", "CF15-", , , , 1)
  [B][COLOR="#008000"]If x <> "False" Then[/COLOR][/B]
    MkDir "c:\Sales Engineering\Cash Flow Projections\" & x
    ChDir "c:\Sales Engineering\Cash Flow Projections"
    ChDir "c:\Sales Engineering\Cash Flow Projections\" & x
  [B][COLOR="#008000"]End If[/COLOR][/B]
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top