-1

I'm new to vba and I've been working on a user form for a Word doc of mine. I'm trying to make a certain combobox select one of its items based on the text of a bookmark that's in the document itself. I'm using an if statement to check the bookmark's text, and if it matches, it should change the combobox's selected item to one that I choose. I have very little knowledge of vba and I'm having trouble getting it to work. I'm not sure where I'm going wrong. Here is my code.

Private Sub UserForm_Initialize()

    Me.cbxShipFrom.AddItem ("My Company")
    Me.cbxShipFrom.AddItem ("Warehouse")
    Me.cbxShipFrom.AddItem ("Warehouse 2")
    Me.cbxShipFrom.AddItem ("Other...")

    If ActiveDocument.Bookmarks("shipfrom").Range.Text = "MY COMPANY" & vbCrLf & "123 BEETLE ST" & vbCrLf & "MYCITY, ST xZIPx" Then
        Me.cbxShipFrom.Value = Me.cbxShipFrom.ListIndex(0)
    End If

End Sub
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Christopher
  • 113
  • 2
  • 2
  • 6

1 Answers1

0

you must use CHR(13) instead of vbCrLf

furthermore:

Me.cbxShipFrom.ListIndex(0)

isn't a valid expression since ListIndex property returns an integer and not an array

you most probably want to use:

Me.cbxShipFrom.List(0)

and return the first element of the combobox list

finally you may want to use the following little refactoring of your code:

Private Sub UserForm_Initialize()
    With Me.cbxShipFrom
        .AddItem ("My Company")
        .AddItem ("Warehouse")
        .AddItem ("Warehouse 2")
        .AddItem ("Other...")
        If ActiveDocument.Bookmarks("shipfrom").Range.Text = "MY COMPANY" & Chr(13) & "123 BEETLE ST" & Chr(13) & "MYCITY, ST xZIPx" Then
            .Value = .List(0)
        End If
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • This worked great! I didn't know about the with statement either, I can see it's potential for condensing down repetitive code. Thanks! – Christopher Dec 17 '16 at 19:17
  • You are welcome. You're right about With statement, which also increases code performance since it avoids multiple memory accesses to the referenced object – user3598756 Dec 17 '16 at 20:11