Select case

[Sandy]

I have four option boxes on a spreadsheet.The following code works fine but
I'd like to have it working in select case, but I don't know how to do it,
any offers?

Private Sub options()
If opt1.Value = True Then
MsgBox ("You chose A")
ElseIf opt2.Value = True Then
MsgBox ("You chose B")
ElseIf opt3.Value = True Then
MsgBox ("You chose C")
ElseIf opt4.Value = True Then
MsgBox ("You chose D")
Else: MsgBox ("You didn't choose any")
End If
End Sub

Sandy

 

[Tom Ogilvy]

Private Sub options()
Select Case True
Case opt1.Value
MsgBox ("You chose A")
Case opt2.Value
MsgBox ("You chose B")
Case opt3.Value
MsgBox ("You chose C")
Case opt4.Value
MsgBox ("You chose D")
Else: MsgBox ("You didn't choose any")
End If
End Sub

 

[RB Smissaert]

One way of doing that:

Private Sub CommandButton1_Click()

Dim i As Byte

On Error Resume Next
For i = 1 To 5
If Controls("OptionButton" & i).Value = True Then
Exit For
End If
Next
On Error GoTo 0

Select Case i
Case 1
MsgBox "option button " & i & " ticked"
Case 2
MsgBox "option button " & i & " ticked"
Case 3
MsgBox "option button " & i & " ticked"
Case 4
MsgBox "option button " & i & " ticked"
Case 5
MsgBox "no option button ticked"
End Select

End Sub

RBS