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.

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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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,

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" wrote in message
...
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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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.

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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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 they are located on a userform.

Thanks

"Rick Rothstein" wrote in message
...
Where are your OptionButtons located... on a UserForm or directly on the
worksheet?

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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.

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.

--
Rick (MVP - Excel)


"MM User" wrote in message
...
Rick they are located on a userform.

Thanks

"Rick Rothstein" wrote in message
...
Where are your OptionButtons located... on a UserForm or directly on the
worksheet?

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"MM User" wrote in message
...
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.