If...Then...ElseIf written as a Select Case Statement

[Sue]

Hello. Select Case Statements confuse me.

The scenario - In MS Word, I have a UserForm with a listing of choices
(AutoText) using OptionButtons. The choice will be inserted at a
bookmark (which I can do outside of the Case statements)

I know it would be much more efficient to write the below as a Select
Case Statement. (There's 44 options.) Can someone get me started?

If UserForm1.OptionButton1.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atAtlanta").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With

ElseIf UserForm1.OptionButton2.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atBellevue").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With
End If

Thanks so much.
Sue Hunter

 

[Jay Freedman]

The Select Case statement requires one expression that may evaluate to
multiple values, and the cases are written for those values -- they
aren't individual tests of multiple expressions. That makes it a
little hard to apply to this kind of situation.

What you can do is create a variable to represent the selected
button's index, and loop through the collection of option buttons to
find the selected one, assigning its index to the variable. Then the
Select Case can use the variable as its expression. It's easier to
show an example (for only 3 buttons, not 44!) than to say this in
words:

Private Sub CommandButton1_Click()
Const OBcount = 3
Dim i As Long
Dim selectedButton As Long

For i = 1 To OBcount
If Me.Controls("OptionButton" & i).Value Then
selectedButton = i
Exit For
End If
Next

Select Case selectedButton
Case 1:
MsgBox "one"
Case 2:
MsgBox "two"
Case 3:
MsgBox "three"
Case Else
MsgBox "error"
End Select
End Sub

The key to this is the use of the string "OptionButton" & i as an
index into the userform's Controls collection. As long as all the
option buttons are named with the same form and vary only in their
numbers, this will work.