Radio button not released

[Francis Hookam]

Radio button not released

Long time XL user but only recent self taught UserForms user

My radio buttons seem to be on until another is clicked - how can I make go
off after they have called their macro? When the UserForm next opened same
radio button is still selected but when clicked there¹s no reaction - other
buttons work ok

Normally this does not matter but occasionally it is useful to be able to
click the same radio button on reopening the userform

Can you suggest code which will Œun-click¹ the radio button as soon as it is
clicked and acted upon

My amateur code for each radio button is like this:

Private Sub DriverReqd_Click()
UserForm1.Hide
SortDriverReqd
End Sub

and this calls the Module Macro sub ŒSortDriverReqd¹

Francis Hookham

 

[J Laroche]

Francis Hookam wrote on 2005/06/17 14:07:

Radio button not released

Long time XL user but only recent self taught UserForms user

My radio buttons seem to be on until another is clicked - how can I make go
off after they have called their macro? When the UserForm next opened same
radio button is still selected but when clicked there¹s no reaction - other
buttons work ok

Normally this does not matter but occasionally it is useful to be able to
click the same radio button on reopening the userform

Can you suggest code which will Œun-click¹ the radio button as soon as it is
clicked and acted upon

My amateur code for each radio button is like this:

Private Sub DriverReqd_Click()
UserForm1.Hide
SortDriverReqd
End Sub

and this calls the Module Macro sub ŒSortDriverReqd¹

Francis Hookham

Buttons and checkboxes have value False (or 0) when unselected and True (or
1) when selected, and you can modify their status (value) by code. Some
controls can also take the value Null (or any other number than 0 and 1).

Private Sub DriverReqd_Click()
DriverReqd.Value = False ' or 0
UserForm1.Hide
SortDriverReqd
End Sub

Note also that instead of hiding the userform, you can eliminate it
completely. The next time you call it, it (and its controls) will reappear
in its default state. The two approaches have their pros and cons.

Private Sub DriverReqd_Click()
Unload Me ' eliminates the userform from memory
SortDriverReqd
End Sub

JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[J Laroche]

J Laroche wrote on 2005/06/17 15:56:

Francis Hookam wrote on 2005/06/17 14:07:


Buttons and checkboxes have value False (or 0) when unselected and True (or
1) when selected, and you can modify their status (value) by code. Some
controls can also take the value Null (or any other number than 0 and 1).

Private Sub DriverReqd_Click()
DriverReqd.Value = False ' or 0
UserForm1.Hide
SortDriverReqd
End Sub

Note also that instead of hiding the userform, you can eliminate it
completely. The next time you call it, it (and its controls) will reappear
in its default state. The two approaches have their pros and cons.

Private Sub DriverReqd_Click()
Unload Me ' eliminates the userform from memory
SortDriverReqd
End Sub

JL
Mac OS X 10.3.9, Office v.X 10.1.6

I forgot to mention the obvious. A radio button is the wrong device to call
a procedure unrelated to the userform and its other controls. You should be
using a command button.

JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[JE McGimpsey]

Radio button not released

Long time XL user but only recent self taught UserForms user

My radio buttons seem to be on until another is clicked - how can I make go
off after they have called their macro? When the UserForm next opened same
radio button is still selected but when clicked there¹s no reaction - other
buttons work ok

Normally this does not matter but occasionally it is useful to be able to
click the same radio button on reopening the userform

Can you suggest code which will Œun-click¹ the radio button as soon as it is
clicked and acted upon

My amateur code for each radio button is like this:

Private Sub DriverReqd_Click()
UserForm1.Hide
SortDriverReqd
End Sub

and this calls the Module Macro sub ŒSortDriverReqd¹

I'm not sure why you're using option buttons for this - option buttons
should show status or exclusive choices. Once a choice is made, it
shouldn't have to be made again.

It sounds more like you should be using regular buttons or labels.

An option button's _Click() event only fires when the option button's
..Value property is False.

I would normally tell you to set the OptionButton's .Value property to
false in the _Click() event code, but a bug makes that useless. When you
subsequently Hide and Show the userform, the option button no longer
responds to the _Click() event.

Can you do without hiding the userform for now? In that case your code
becomes:

Private Sub DriverReqd_Click()
DriverReqd.Value = False
SortDriverReqd
End Sub

Again, that seem's kludgy - you might as well use a regular button or
label.

 

[J Laroche]

JE McGimpsey wrote on 2005/06/17 18:02:

I would normally tell you to set the OptionButton's .Value property to
false in the _Click() event code, but a bug makes that useless. When you
subsequently Hide and Show the userform, the option button no longer
responds to the _Click() event.

JE, what you say is strange, because hiding doesn't cause any problem for
me. The previous value is still there when the userform is recalled after
hiding it, and the click event works perfectly (if the button's value is
False or Null).

JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[JE McGimpsey]

JE, what you say is strange, because hiding doesn't cause any problem for
me. The previous value is still there when the userform is recalled after
hiding it, and the click event works perfectly (if the button's value is
False or Null).

Hmm... doesn't work for me in either XLv.X (10.1.6) or XL04 (11.1.1).

Here's what I have:

Userform1, containing two OptionButtons (OptionButton1 and
OptionButton2).

In the Userform code module:

Private Sub OptionButton2_Click()
OptionButton2.Value = False
Me.Hide
MsgBox "OptionButton2"
Me.Show
End Sub

Run the Userform. Click OptionButton2. Observe Messagebox and dismiss
it. Userform shows with neither OptionButton selected. Click on
OptionButton2: the button is selected, but the _Click() event doesn't
fire.

Is that not what you see?

Here's my test book:

ftp://ftp.mcgimpsey.com/excel/optionbuttontest.xls

 

[J Laroche]

JE McGimpsey wrote on 2005/06/17 19:15:

Hmm... doesn't work for me in either XLv.X (10.1.6) or XL04 (11.1.1).

Here's what I have:

Userform1, containing two OptionButtons (OptionButton1 and
OptionButton2).

In the Userform code module:

Private Sub OptionButton2_Click()
OptionButton2.Value = False
Me.Hide
MsgBox "OptionButton2"
Me.Show
End Sub

Run the Userform. Click OptionButton2. Observe Messagebox and dismiss
it. Userform shows with neither OptionButton selected. Click on
OptionButton2: the button is selected, but the _Click() event doesn't
fire.

Is that not what you see?

Here's my test book:

ftp://ftp.mcgimpsey.com/excel/optionbuttontest.xls

I see what you mean, and yes I agree with you based on your example. But if
I remove Me.Show from your procedure and let Button1_Click alone recall the
userform (in its previous condition), then OptionButton2_Click fires
correctly. That's how Francis's code was operating, and how I tested it. I
don't see why you re-show the userform as you do. Once you hide it, wouldn't
you want to show it again based only on an external stimulus? I've
programmed a lot, but you have done much more; in what situation would what
you've done be useful? I guess my programs have never been sophisticated
enough to make use of that.

There's a workaround to OptionButton2_Click though:
Private Sub OptionButton2_Change()
If OptionButton2.Value = True Then
Me.Hide
MsgBox "OptionButton2 has been clicked"
OptionButton2.Value = False
Me.Show
Else
' do nothing, OptionButton2 has not been clicked
End If
End Sub

JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[JE McGimpsey]

I see what you mean, and yes I agree with you based on your example. But if
I remove Me.Show from your procedure and let Button1_Click alone recall the
userform (in its previous condition), then OptionButton2_Click fires
correctly.

That's true, but I try not to recommend a solution that only works some
of the time, especially when I haven't characterized it well.

That's how Francis's code was operating, and how I tested it.

I didn't know that. The OP's statement was "When the UserForm next
opened same radio button is still selected", which didn't indicate to me
HOW it was "opened".

I don't see why you re-show the userform as you do. Once you hide it,
wouldn't you want to show it again based only on an external
stimulus? I've programmed a lot, but you have done much more; in what
situation would what you've done be useful? I guess my programs have
never been sophisticated enough to make use of that.

I've used the technique many times, where I've had buttons on UserForms
call a second UserForm, hiding the first one for aesthetic reasons.
Since all Mac UserForms are modal, the first one will wait for the
second one to exit before running it's code, much like the Messagebox.

One example: I have an analogue of Word's Work Menu as an add-in.
There's a Userform that I use to allow the user to assign short names to
the files in the Work menu, and to validate that all of the items still
refer to a current workbook. If during validation, any "orphan" work
menu items are found, a second userform pops up with the list, and the
user is given the option of deleting the item from the menu, or browsing
to find the item. While the second userform is visible, the first one is
hidden.