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

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.


"Jay Freedman" wrote:

On Sun, 20 Apr 2008 17:18:01 -0700, StevenM <StevenM@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

.