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

Discussion in 'Word VBA Beginners' started by Sue, Oct 10, 2011.

  1. Sue

    Sue Guest

    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
     
    Sue, Oct 10, 2011
    #1
    1. Advertisements

  2. Sue

    Jay Freedman Guest

    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.

    On Mon, 10 Oct 2011 13:46:31 -0700 (PDT), Sue <>
    wrote:

    >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


    --
    Regards,
    Jay Freedman
    Microsoft Word MVP
    Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
     
    Jay Freedman, Oct 26, 2011
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Alder

    Change case of string variable

    Alder, Sep 8, 2003, in forum: Word VBA Beginners
    Replies:
    1
    Views:
    107
    Alder
    Sep 8, 2003
  2. Thejan Mendis

    TEXT conert to Title Case

    Thejan Mendis, Oct 8, 2003, in forum: Word VBA Beginners
    Replies:
    2
    Views:
    113
    Mark Tangard
    Oct 8, 2003
  3. Rita King

    Could this code be better written?

    Rita King, Nov 10, 2003, in forum: Word VBA Beginners
    Replies:
    0
    Views:
    99
    Rita King
    Nov 10, 2003
  4. tbl
    Replies:
    4
    Views:
    172
    Word Heretic
    Jan 29, 2004
  5. Geoff Edwards

    If the or Select Case?

    Geoff Edwards, Apr 1, 2004, in forum: Word VBA Beginners
    Replies:
    4
    Views:
    125
    Jerry Bodoff
    Apr 2, 2004
  6. Sophia
    Replies:
    6
    Views:
    147
    Sophia
    Jul 1, 2004
  7. danny
    Replies:
    13
    Views:
    236
    Karl E. Peterson
    Mar 6, 2007
  8. Select Case Statement

    , Apr 24, 2009, in forum: Word VBA Beginners
    Replies:
    3
    Views:
    279
Loading...