Cancel button on userform

[KCIWebMaster]

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

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.

 

[pcc]

Probably need

Unload Me
End

 

[KCIWebMaster]

OK, another stupid question:

how do I check if the cancel button was clicked?

Can I add an if statement like

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.

 

[pcc]

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

 

[sijpie]

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?

 

[RoryA]

Add a public variable to the form module, something like:

Public blnCancel as Boolean

then in your OK button, you use:

Private sub cmdOK_Click()
   blnCancel = False
   Me.Hide
End Sub

and your Cancel button is:

Private sub cmdOK_Click()
   blnCancel = True
   Me.Hide
End Sub

then your calling code looks like:

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.

 

[sijpie]

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

Great tag line by the way.

 

[RoryA]

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!

 

[ataloss]

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.

 

[Rick Rothstein]

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"
[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.