Hi -

I'm trying to clean up some code related to OptionButtons on a
UserForm. Depending on what button someone selects, a variable that is
used elsewhere in the code gets set.

Here's my inefficient code:

If UserForm.OptionButton1.Value = True Then
b = 0
End If
..
..
.. and so on until
If UserForm.OptionButton12.Value = True Then
b = 11
End If

I suspect there is some way to do this with Select Case, but I haven't
been able to get it to work yet...

Thanks in advance

Hi

Don't confuse "long code" or "boring to write" with "unefficient". Your code
shuld run pretty fast, you won't even notice it's there. Real userfriendly
code is often ridicolously long and ugly since it can deal with about
everything that copuld possibly happen.

I'd set a value in all the optionbutton's click events:

Dim B As Long

Private Sub OptionButton1_Click()
B = 1
End Sub

Private Sub OptionButton2_Click()
B = 2
End Sub

.... but that's a question of style and habits, not of efficiency. Other ways
might be to subclass the controls or to put them into a custom collection
and loop that. I doubt any of the methods would gain any speed at all, they
would definitely use more memory though.

HTH. best wishes Harald


" skrev i melding
...
Hi -

I'm trying to clean up some code related to OptionButtons on a
UserForm. Depending on what button someone selects, a variable that is
used elsewhere in the code gets set.

Here's my inefficient code:

If UserForm.OptionButton1.Value = True Then
b = 0
End If
.
.
. and so on until
If UserForm.OptionButton12.Value = True Then
b = 11
End If

I suspect there is some way to do this with Select Case, but I haven't
been able to get it to work yet...

Thanks in advance

for i = 1 to 12
if Userform.Controls("OptionButton" & i).Value = True then
b = i - 1
end if
Next

--
Regards,
Tom Ogilvy

" wrote in message
...
Hi -

I'm trying to clean up some code related to OptionButtons on a
UserForm. Depending on what button someone selects, a variable that is
used elsewhere in the code gets set.

Here's my inefficient code:

If UserForm.OptionButton1.Value = True Then
b = 0
End If
.
.
. and so on until
If UserForm.OptionButton12.Value = True Then
b = 11
End If

I suspect there is some way to do this with Select Case, but I haven't
been able to get it to work yet...

Thanks in advance

Marston,

If all of your optionbuttons are in a frame (Frame1 assumed below) then I
think this would work:

Dim ctl As Control
Dim optbutton As msforms.OptionButton

For Each ctl In UserForm.Frame1.Controls
If TypeOf ctl Is msforms.OptionButton Then
Set optbutton = ctl
If optbutton.Value = True Then
b = Mid$(optbutton.Caption, 13) - 1
End If
End If
Next ctl

hth,

Doug Glancy

" wrote in message
...
Hi -

I'm trying to clean up some code related to OptionButtons on a
UserForm. Depending on what button someone selects, a variable that is
used elsewhere in the code gets set.

Here's my inefficient code:

If UserForm.OptionButton1.Value = True Then
b = 0
End If
.
.
. and so on until
If UserForm.OptionButton12.Value = True Then
b = 11
End If

I suspect there is some way to do this with Select Case, but I haven't
been able to get it to work yet...

Thanks in advance

Hi Marston;

I agree, if it ain't broke don't fix it.

Thanks,

Greg
-----Original Message-----
Hi

Don't confuse "long code" or "boring to write"
with "unefficient". Your code
shuld run pretty fast, you won't even notice it's there.
Real userfriendly
code is often ridicolously long and ugly since it can
deal with about
everything that copuld possibly happen.

I'd set a value in all the optionbutton's click events:

Dim B As Long

Private Sub OptionButton1_Click()
B = 1
End Sub

Private Sub OptionButton2_Click()
B = 2
End Sub

.... but that's a question of style and habits, not of
efficiency. Other ways
might be to subclass the controls or to put them into a
custom collection
and loop that. I doubt any of the methods would gain any
speed at all, they
would definitely use more memory though.

HTH. best wishes Harald


"
skrev i melding
...
Hi -

I'm trying to clean up some code related to
OptionButtons on a
UserForm. Depending on what button someone selects, a
variable that is
used elsewhere in the code gets set.

Here's my inefficient code:

If UserForm.OptionButton1.Value = True Then
b = 0
End If
.
.
. and so on until
If UserForm.OptionButton12.Value = True Then
b = 11
End If

I suspect there is some way to do this with Select
Case, but I haven't
been able to get it to work yet...

Thanks in advance



.