MrExcel Publishing
Your One Stop for Excel Tips & Solutions

ther must be a better way!


Posted by Slim on February 06, 2002 7:15 AM

Ok

I have created a userform for the return of loan items. I want to select the correct sheet based on a form with option buttons.

I have created the following code which works fine but to me isn't very elegant. Is there a better way?

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 Jacob on February 06, 2002 8:43 AM

Hi

You can use

Select case true

case OptionButton1.Value


case OptionButton2.Value


etc etc

end select

-Jacob

Posted by Paul on February 06, 2002 10:15 AM

Format the cell to show AM & PM