There must be a more elegant way
Posted by Slim on February 08, 2002 3:10 AM
I have created the following for part of a loan system project. The code works fine, but it looks horrible. Not being used to using the optionbuttons can anyone tell me a more elegant way similar to the select case that i use for comboboxes?
Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
Worksheets("juniorwheelchair").Select
Else
If OptionButton2.Value = True Then
Worksheets("smallwheelchair").Select
Else
If OptionButton3.Value = True Then
Worksheets("mediumwheelchair").Select
Else
If OptionButton4.Value = True Then
Worksheets("largewheelchair").Select
Else
If OptionButton5.Value = True Then
Worksheets("juniorcrutch").Select
Else
If OptionButton6.Value = True Then
Worksheets("adultcrutch").Select
Else
If OptionButton7.Value = True Then
Worksheets("juniorelbowcrutch").Select
Else
If OptionButton8.Value = True Then
Worksheets("adultelbowcrutch").Select
Else
If OptionButton9.Value = True Then
Worksheets("juniorneckcollar").Select
Else
If OptionButton10.Value = True Then
Worksheets("adultneckcollar").Select
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Posted by Mudface on February 08, 2002 3:34 AM
You could try something like
If OptionButton1 Then ....
If OptionButton2 Then ....
etc.
Still not very elegant and I'm sure there's a better way, but it tidies up your code quite a bit.
Posted by DK on February 08, 2002 3:42 AM
Here's one way:-
Option buttons have a property called Tag which can be used for situations like this. For each option button change the Tag property to the sheet name it relates to e.g. for OptionButton1 change Tag to juniorwheelchair, OptionButton2 change Tag to smallwheelchair and so on. Then replace your code with this:-
Private Sub CommandButton1_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "OptionButton" And ctl.Value = True Then Exit For
Next
Sheets(ctl.Tag).Select
End Sub
If there are other option buttons on your form/sheet other than the 10 in your code then you should assign the 10 buttons to a group and use something like:-
If TypeName(ctl) = "OptionButton" And ctl.Value And ctl.GroupName = "MyGroup" = True Then Exit For
If you do use this then be sure to put in On Error Resume Next in because not all controls have a Group property.
Any probs, let me know,
Regards,
D
Posted by slim on February 08, 2002 6:15 AM
message for DK- not quite working
Dear D
have entered the code exactly as below(is this correct)
When run it gives the following error message
a module is not a valid type
any ideas?
Posted by DK on February 08, 2002 7:59 AM
Re: message for DK- not quite working
Can you post the code...
Regards,
D
