VBA How to select combo box value based on given value

I have a Word VBA form that contains a combo/drop-down list box. The selections in this box are loaded in the VBA code via an array.

When the form loads I want a particular value to be selected based on the value previously stored in that box which I have saved (for simplicity here) to  a variable called MyValue.

Therefore the selection I want displayed is that with the numerical value MyValue.

The combobox control has two columns, the bound column (numeric) is 1 but only column 2 (text) is displayed (colmn widths 0 pt;200 pt).
For instance the values/displayed text might be:
12/Choice 1
14/Choice 2
17/Choice 3
27/Choice 4
but the actual values in the control can vary each time as can the number and position of the choices.

If say I have a MyValue of 17 then I want Choice 3 displayed.

I can see how to do this using a positional reference, in this case it would be (2), assuming option base zero, but I dont know that MyValue 17 will be in position (2) because this can change.

The question therefore is how to select the displayed value I want based on the MyValue I am passing to the form?
LawyerLudditesolicitorAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
Just step through the List until the value is found
Dim c As Integer
Dim MyValue As String
MyValue = "MyValue"
For c = 0 To ComboBox1.ListCount - 1
    If ComboBox1.List(c, 1) = MyValue Then
        ComboBox1.ListIndex = c
        Exit For
    End If
Next c

Open in new window

0
 
LawyerLudditesolicitorAuthor Commented:
Thanks. That will do it.  I do find it strange that VBA seem to lack the ability to set values by a simple assignment rather than the inefficient requirement to loop through every value until you get a match.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.