How Does One Determine If a Command Button Has Been Pressed?

[StevenM]

I’ve created a user form with three sets of option buttons (one set with 6
option buttons, another set with 3 options buttons, and another set with 3
options buttons), a text box, and two command buttons. I believe I’ve got
everything to work, except for the command buttons.

The code with the user form contains:
Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Me.Hide
End Sub

The code in the adjacent module contains:

Sub TestUserForm()
UserForm1.Show
If UserForm1.CommandButton2 = True Then
Exit Sub
End If
If UserForm1.OptionButton1 = True Then
MsgBox "BorderStyle #1â€
End If
<etc.>
End Sub

The phrase “UserForm1.OptionButton1 = True†tells me if this option has been
selected, and the phrase “sString = UserForm1.TextBox1.Text†gives me the
text entered into the text box. But the phrase “UserForm1.CommandButton2 =
True†is always false even if that command button has been pressed.

So, could someone be so kind as to explain to me how one determines if the
command button has been pressed on a user form?

Steven Craig Miller

 

[Gregory K. Maxey]

Steven,

AFAIK the command buttons don't return a value directly. What you can do is
set the tag property of the userform with the Command button click.
Something like this:

Sub TestForm()
Dim oFrm As UserForm1
Set oFrm = New UserForm1
oFrm.Show
MsgBox oFrm.TextBox1
MsgBox oFrm.OptionButton1
MsgBox oFrm.Tag
Unload oFrm
Set oFrm = Nothing
End Sub

Option Explicit
Private Sub CommandButton1_Click()
Me.Tag = "CB1 Clicked"
Me.Hide
End Sub
Private Sub CommandButton2_Click()
Me.Tag = "CB2 Clicked"
Me.Hide
End Sub

 

[Tony Strazzeri]

Hi Steven,

You can't really test CommandButton controls this way. They are true
only for the duration of the click. Are you trying to determine if
the user pressed a cancel button or are confusing the action with that
of a ToggleButton?

Try the following code, it may address what you are wanting to do.
You will need to have two toggle buttons, two option buttons and three
command buttons all called by their default names e.g. CommandButton1
etc.

Note the use of a variable I created to pass along whether or not the
user clicked the Cancel button.


Hope this helps.
Cheers
TonyS.

In the calling module:
================
Sub TestUserForm()
UserForm1.Show
With UserForm1
MsgBox "Back in the Calling Module" & vbCr _
& "The current button states are as follows" & vbCr _
& "OptionButton 1=" & .OptionButton1 & vbTab &
"OptionButton 2=" & .OptionButton2 & vbCr _
& vbCr & "ToggleButton1=" & .ToggleButton1 & vbTab &
"ToggleButton2=" & .ToggleButton2 & vbCr _
& vbCr & "CommandButton1=" & .CommandButton1 & vbTab &
"CommandButton2=" & .CommandButton2

If .UserCancel = True Then
MsgBox "You pressed the Cancel button"
Else
If .OptionButton1 = True Then
MsgBox "BorderStyle #1”"
End If
End If
End With

Unload UserForm1
End Sub


In the Userform module
=================
Public UserCancel As Boolean

Private Sub CommandButton1_Click()
UserCancel = False
Me.Hide
End Sub

Private Sub CommandButton2_Click()
MsgBox "Hi! Status from the Form module" & vbCr & "============="
_
& vbCr & "You pressed the Status button" & vbCr _
& "OptionButton 1=" & OptionButton1 & vbTab &
"OptionButton 2=" & OptionButton2 & vbCr _
& vbCr & "ToggleButton1=" & ToggleButton1 & vbTab &
"ToggleButton2=" & ToggleButton2 & vbCr _
& vbCr & "CommandButton1=" & CommandButton1 & vbTab &
"CommandButton2=" & CommandButton2
End Sub


Private Sub CommandButton3_Click()
UserCancel = True
Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
CommandButton1.Caption = "Close"
CommandButton2.Caption = "Status"
CommandButton3.Caption = "Cancel"
End Sub

 

[Jay Freedman]

I’ve created a user form with three sets of option buttons (one set with 6
option buttons, another set with 3 options buttons, and another set with 3
options buttons), a text box, and two command buttons. I believe I’ve got
everything to work, except for the command buttons.

The code with the user form contains:
Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Me.Hide
End Sub

The code in the adjacent module contains:

Sub TestUserForm()
UserForm1.Show
If UserForm1.CommandButton2 = True Then
Exit Sub
End If
If UserForm1.OptionButton1 = True Then
MsgBox "BorderStyle #1”
End If
<etc.>
End Sub

The phrase “UserForm1.OptionButton1 = True” tells me if this option has been
selected, and the phrase “sString = UserForm1.TextBox1.Text” gives me the
text entered into the text box. But the phrase “UserForm1.CommandButton2 =
True” is always false even if that command button has been pressed.

So, could someone be so kind as to explain to me how one determines if the
command button has been pressed on a user form?

Steven Craig Miller

I'm not sure how you would find out this information if you didn't know it --
other than asking here, so I guess you did it right.

The expression UserForm1.CommandButton2 really refers to the "default property"
of the commandbutton, which is its .Value property. When you look up the help
topic about the .Value property for forms controls, you'll find that for a
commandbutton the property is always False. That is, although it's defined and
thus won't throw an error if you refer to it, it's useless.

The problem is that a commandbutton doesn't have a persistent state ("selected"
or "clicked") the way an optionbutton does. All it has is a click event, which
fires the click procedure and then disappears. If you want a persistent
indication of whether the commandbutton has been clicked, you have to create it
yourself.

One way is to declare a module-level Boolean variable in the userform (that is,
place the Dim statement at the top of the userform's code, outside any
procedures) and manipulate its value as needed. You could rely on the fact that
Boolean variables are initialized to False when they're declared, or you can put
an explicit assignment of False in the Userform_Initialize() procedure. Place a
statement in the CommandButton2_Click() procedure to set the variable's value to
True. In the module that calls the userform, the module-level variable is
addressed as a member of the userform just like the buttons.

From the code you showed, I'm guessing that CommandButton2 is really the Cancel
button, so in this example I'll name the variable IsCancelled.

The code with the user form contains:

Dim IsCancelled As Boolean ' <=====

Private Sub CommandButton1_Click()
' IsCancelled = False by default
Me.Hide
End Sub

Private Sub CommandButton2_Click()
IsCancelled = True ' <=====
Me.Hide
End Sub

The code in the adjacent module contains:

Sub TestUserForm()
UserForm1.Show
If UserForm1.IsCancelled = True Then
Exit Sub
End If
If UserForm1.OptionButton1 = True Then ' <======
MsgBox "BorderStyle #1”
End If
<etc.>
End Sub

Just for completeness, I'll mention that there are other ways to do this job.
For example, the CommandButton2_Click() procedure could change the caption or
background color of the button and the module code could examine that property.
I don't think I've ever seen anyone do that, but it's possible.

 

[Gordon Bentley-Mix]

Steven,

The approach I take is a variation on the process recommended by Jean-Guy
Marcil in one of my previous posts, which is similar to that described by Jay
Freeman.

I create a Public variable in the module that calls the UserForm and then
set the value of this variable in the Click event for the different buttons.
(Note that I usually have only two buttons on a form - "OK" [or some
variation thereon] and "Cancel" - so I can get away with a Boolean variable.
Your mileage may vary...) This is very much like what Jay does with his
module-level variable for the UserForm, but I prefer the Public variable
because it lets me get away with not having to record the value from the
UserForm variable in another variable in the calling module. I just have to
be careful to make the name of this variable as descriptive as possible and
*never* modify its value anywhere but in the Click events of the buttons.

I also don't like to rely on default values for variables, so I always
explicitly set the value in each button's Click event.

As for Jay's comment about evaluating the caption of a button, I actually do
this in several of my templates. In these templates I have a button that
changes caption from "Add" to "Update" when the user selects a value from a
ListBox to edit. However, I only evaluate the caption to determine what it
should be after the button is clicked; i.e. to change it back from "Update"
to "Add" again. For determining the action that should be performed on the
Click event, I use a module-level Boolean variable, the value of which is set
to "True" when the user selects a value to edit and back to "False" when the
"Update" button is clicked. Much easier and more reliable than trying to
evaluate a property of a control.

--
Cheers!
Gordon
The Kiwi Koder

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[StevenM]

Thank you everyone for your suggestions (some I’m still digesting). I
implemented the suggestion by Gregory K. Maxey. Everything seems to work, I
was able to determine which command button was pressed by using a Tag. But
when the “x†at the top right hand corner of the User Form is clicked, then I
receive an error message. The macro chokes on the statement:

If .Tag = "Cancel" Then

I had:

Private Sub CommandButton1_Click()
Me.Hide
Me.Tag = "Ok"
End Sub

Private Sub CommandButton2_Click()
Me.Hide
Me.Tag = "Cancel"
End Sub

Sub TestUserForm()
Dim oForm As UserForm1
Set oForm = New UserForm1
With oForm
‘Initialize form
..OptionButton6 = True
..OptionButton7 = True
..Show
‘Now cancel macro if the cancel button was pushed.
If .Tag = "Cancel" Then
Exit Sub
End If
<etc.>

As I said, everything worked fine. The cancel command button exited the
macro, otherwise the macro continued on to do its tasks. The only problem is
that when I click the “x†at the top right hand corner of the user form the
statement “If .Tag = "Cancel" Then†creates a run-time error: ‘-2147418105
(80010007).’ I tried moving the statement around my program to see if it was
the problem. And it was. I substituted the statement: “sString = .Tag†and it
choked on that also. I tried adding “.Tag = “Okâ€â€ after “’Initialize formâ€
and that didn’t help any.

This creates two problems. What can I do to stop it from choking on the .Tag
statement when the “x†at the top right hand corner of the user form is
clicked? And how can I get the macro to stop if the “x†is clicked?

I added an error handler, and that solved both problems. When the “x†was
clicked I was able to exit the macro without seeing the error. But is that
the way I’m supposed to handle this problem?

Steven Craig Miller

 

[StevenM]

Jay,

I tried your suggestion:

The code with the user form contains:

Dim IsCancelled As Boolean ' <=====

Private Sub CommandButton1_Click()
' IsCancelled = False by default
Me.Hide
End Sub

Private Sub CommandButton2_Click()
IsCancelled = True ' <=====
Me.Hide
End Sub

The code in the adjacent module contains:

Sub TestUserForm()
UserForm1.Show
If UserForm1.IsCancelled = True Then
Exit Sub
End If

But the statement “UserForm1.IsCancelled†created a compile error: “Method
or data member not found.†Did I do something wrong?

Steven Craig Miller

 

[Gregory K. Maxey]

Steven,

I am not a definative source on how things should be done. I can only say
that I wouldn't write my code like you are trying to do. I try to keep my
calling macro as simple as possible and then do the work with other
procedures:


Sub TestUserForm()
Dim oForm As UserForm1
Set oForm = New UserForm1
oForm.Show
Unload oForm
Set oForm = Nothing
End Sub

And yes it could be even simplier:

Sub TestUserForm()
Userform1.Show
End Sub

but I try to stay in the habit of explicitly declaring the form.

You can initialize the form in the userform project:

Option Explicit

Private Sub UserForm_Initialize()
With Me
.OptionButton6 = True
.OptionButton7 = True
End With
End Sub

'Here is the cancel command button code:

Private Sub CommandButton2_Click()
Me.Hide
End Sub

Here is the OK command button code:

Private Sub CommandButton1_Click()
'Put your action code here
MsgBox "Command Button 1 clicked"
Msgbox Me.Textbox1
'Or put your action code in a separate procedure in this project
ActionCode
Module1.ActionCodeII
'Or pass a reference of your form to a separate procedure in the calling
module
Module1.ActionCodeII Me
Me.Hide
End Sub

'Procedure in Userform1 module
Sub ActionCode()
MsgBox "Command Button 1 clicked"\
MsgBox Me.Textbox1
End Sub

'Procedure in calling module
Sub ActionCodeII(ByVal oFrm As UserForm1)
MsgBox "Command Button 1 clicked"
MsgBox oFrm.TextBox1
End Sub
Sub ActionCode()
MsgBox "Command Button 1 clicked"
End Sub

 

[StevenM]

To: Gregory K. Maxey,

Wow! Although (IMHO) counter-intuitive, that worked!

The calling macro becomes (more or less) a shell for showing the user form.
The OK command button becomes the place where all the data is collected from
the user form. And then that data is passed on to another function which
deals with this information. Doing it that way dispenses with the need for a
..Tag (since it only runs if OK was pressed) and it also eliminates the
problem with someone clicking the “x†(at the top right corner of the form)
to close the form (since there is nothing else to do in the calling macro but
shut down--and there is no error). Thanks a lot for the help! I greatly
appreciate it!

Steven Craig Miller

 

[Jay Freedman]

Jay,

I tried your suggestion:


But the statement "UserForm1.IsCancelled" created a compile error:
"Method or data member not found." Did I do something wrong?

Steven Craig Miller

No, you didn't -- I did. Change the keyword in the declaration of
IsCancelled from Dim to Public. That makes the variable visible in the
calling macro.

Sorry for the confusion.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Gregory K. Maxey]

Glad I could help.

 

[Tony Strazzeri]

You have to declare IsCancelled as a public variable in the Userform
Genreal definitions area first.
ie. place this before any form procedures.

Hope this helps.
Cheers!

TonyS.

Public IsCancelled as Boolean