Form - Get Selected (true) value of Option Button

[MM User]

Hi,

I have a form that contains several option buttons (all are in the same
groupname), I would like to make this easier to manage so is it possible to
that after the user has selected a option button and clicked the OK button
to search the form for which option button is selected ? this way I could
use a case statement.

Thanks in advance.

 

[Rick Rothstein]

You could use something like this...

Private Sub CommandButton1_Click()
Dim OB As msforms.OptionButton
Dim SelectedOB As msforms.OptionButton
On Error Resume Next
For Each OB In Controls
If TypeOf OB Is msforms.OptionButton Then
If OB.GroupName = "MyGroupName" Then
If OB.Value = True Then
Set SelectedOB = OB
Exit For
End If
End If
End If
Next
'
' Your code would go here instead of what follows. Use the SelectedOB
' object to get the Name, Caption or whatever other property of the
' selected OptionButton you may want to use
'
If Err.Number > 0 Then
MsgBox "No OptionButton selected"
Else
MsgBox SelectedOB.Name & " -- " & SelectedOB.Caption
End If
End Sub

 

[MM User]

Rick,

I can't seem to get this working - I have changed the GroupName to
"switchgp" but keep getting:

"No OptionButton selected"

is there anything else needs changing?

Thanks again!

 

[Rick Rothstein]

Where are your OptionButtons located... on a UserForm or directly on the
worksheet?

 

[MM User]

Rick they are located on a userform.

Thanks

Where are your OptionButtons located... on a UserForm or directly on the
worksheet?

 

[Rick Rothstein]

Okay, I used a UserForm for my test also and the code worked for me. Off the
top of my head, for my GroupName line which you modified to this...

If OB.GroupName = "switchgp" Then

Is that **exactly** (letter case and all) what you used for the GroupName? I
ask because the If test is case sensitive.

 

[MM User]

Rick,

Yes I have checked the groupname,

Not sure what I am doing wrong - I even tried to create a new form and put
add the code but no luck.

Regards,

 

[Dave Peterson]

This variation of Rick's code worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()
Dim OB As Control
Dim SelectedOB As msforms.OptionButton

Set SelectedOB = Nothing

For Each OB In Me.Controls
If TypeOf OB Is msforms.OptionButton Then
If OB.GroupName = "switchgp" Then
If OB.Value = True Then
Set SelectedOB = OB
Exit For
End If
End If
End If
Next
'
' Your code would go here instead of what follows. Use the SelectedOB
' object to get the Name, Caption or whatever other property of the
' selected OptionButton you may want to use
'
If SelectedOB Is Nothing Then
MsgBox "No OptionButton selected"
Else
MsgBox SelectedOB.Name & " -- " & SelectedOB.Caption
End If
End Sub

 

[Rick Rothstein]

Alright, if you are getting the "No OptionButton selected" message, that
means an error is taking place but the On Error Resume Next is blocking it.
Comment out the On Error line and see what line the program raises the error
at... perhaps that will tell us something. And, just to be sure... you did
copy/paste my code, not re-type it, correct?

 

[Rick Rothstein]

Out of curiosity, did you have trouble with the code if you don't set
SelectedOB to Nothing? That step should not be necessary as Nothing is the
default for the object before it is set to anything.

 

[MM User]

Greg/Rick,

Thanks Greg's amended version works-

I tried commenting out the 'On Error Resume Next'

It seems to get stuck at
'For Each OB In Controls'

Could this be to do with being Excel 2003 if you are using 2007?

I have your Greg's version working anyway but just curious

Regards

 

[Dave Peterson]

It's become a habit.

Lots of time code in the newsgroup gets copied and pasted into who knows what.
And if the person who takes that code decides to make it into some sort of loop,
then the OB may not be nothing (to start) the second time through the loop.

 

[Dave Peterson]

Who's Greg?

<vbg>

Greg/Rick,

Thanks Greg's amended version works-

I tried commenting out the 'On Error Resume Next'

It seems to get stuck at
'For Each OB In Controls'

Could this be to do with being Excel 2003 if you are using 2007?

I have your Greg's version working anyway but just curious

Regards

 

[MM User]

Apologies I meant Dave - I did copy and paste the code by the way!

 

[Rick Rothstein]

I'm glad you got everything straightened out. I'm curious though... when you
get a chance, could you comment out the...

Set SelectedOB = Nothing

statement that Dave introduced and then run your UserForm... does it fail
with that statement commented out? Oh, and I am using XL2003 also.

--
Rick (MVP - Excel)

Apologies I meant Dave - I did copy and paste the code by the way!

 

[Dave Peterson]

It won't fail with that line commented out.

But I did have to change a declaration:
Dim OB as Control
to get this to work
For Each OB In me.Controls

I'm glad you got everything straightened out. I'm curious though... when you
get a chance, could you comment out the...

Set SelectedOB = Nothing

statement that Dave introduced and then run your UserForm... does it fail
with that statement commented out? Oh, and I am using XL2003 also.

 

[Rick Rothstein]

Hmm! I missed that when I looked at your code. Obviously I ended up doing it
the harder way, but why do you think my using

As MSForms.OptionButton

instead of

Me.Controls

worked for me and not for your or the OP? I mean, I am not using anything
strange here... XL2003, a UserForm and standard controls from the Toolbox. I
also note your way eliminated the need for the On Error trap I was forced to
use.

 

[Dave Peterson]

You mean "instead of As Control", right?

I guessed that that "on error resume Next" masked the error in the "for each ob
...." line.

(I was surprised that it worked for you!)