Search code examples
excelvbacombobox

Reverse ComboBox: How to programmatically select an item in ActiveX ComboBox based on a TextBox value?


I have a multi-column Combobox, and tried to search for a codes for awhile now, but to no avail.

Here's my combobox looks like:

  emp_id         emp_name
 ---------    ---------------
  <blank>      - Select -
     1         James Bond
     2         Jason Statham
     3         Sylvester S.
     4         Chuck Norris

where columnwidths = "0,100" and and columnheads = False, so it will only show the emp_name

What I'm trying to do is, programmatically select and display an item from the combobox based on a variable

So far the closest logic to what I'm trying to achieve is this:

empSysID = 3 '~This is Sylvester S.
With .ComboBoxEmployee
    For i = 0 To .ListCount - 1
        If .Column(0, i) = empSysID Then
            .Value = .List(i) '~Trying to select and display 'Sylvester S.' in combobox
            Exit For
        End If
    Next i
End With

But I'm getting Run-time error '380': Could not set the Value Property. Invalid property value.

Thank you in advance!


Solution

  • You need to use ListIndex:

    empSysID = 3 '~This is Sylvester S.
    With .ComboBoxEmployee
        For i = 0 To .ListCount - 1
            If .Column(0, i) = empSysID Then
                .ListIndex = i 
                Exit For
            End If
        Next i
    End With