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.

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,


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

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



MM User wrote:

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,

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




--

Dave Peterson

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 (MVP - Excel)


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


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

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.

--
Rick (MVP - Excel)


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



MM User wrote:

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,

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




--

Dave Peterson

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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


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

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.



Rick Rothstein wrote:

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.

--
Rick (MVP - Excel)

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



MM User wrote:

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,

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




--

Dave Peterson

--

Dave Peterson

Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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


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





--

Dave Peterson

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

"Dave Peterson" wrote in message
...
Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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


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





--

Dave Peterson

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)


"MM User" wrote in message
...
Apologies I meant Dave - I did copy and paste the code by the way!

"Dave Peterson" wrote in message
...
Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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


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





--

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

Rick Rothstein wrote:

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)

"MM User" wrote in message
...
Apologies I meant Dave - I did copy and paste the code by the way!

"Dave Peterson" wrote in message
...
Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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


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





--

Dave Peterson


--

Dave Peterson

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.

--
Rick (MVP - Excel)


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

Rick Rothstein wrote:

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)

"MM User" wrote in message
...
Apologies I meant Dave - I did copy and paste the code by the way!

"Dave Peterson" wrote in message
...
Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in
message
...
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 (MVP - Excel)


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


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





--

Dave Peterson


--

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!)



Rick Rothstein wrote:

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.

--
Rick (MVP - Excel)

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

Rick Rothstein wrote:

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)

"MM User" wrote in message
...
Apologies I meant Dave - I did copy and paste the code by the way!

"Dave Peterson" wrote in message
...
Who's Greg?

<vbg

MM User wrote:

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

"Rick Rothstein" wrote in
message
...
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 (MVP - Excel)


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


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





--

Dave Peterson


--

Dave Peterson

--

Dave Peterson