Excel VBA textbox to populate combobox

Question

I'm after some help that has had me stumped for a while. Excuse the long explanation.

I have a combobox that populates from a range when the userform initializes. When I type into the combobox the preemptive text appears as it is supposed to. I then have a Change event for a textbox which populates based on what gets typed into the combobox. That part all works fine (I got that code from another site).

I have two ways to enter the data into the combobox, one is by typing, and the other is when the text in another text box changes, it also populates the combobox. I do this by "combobox1 = textbox1.value". Now here is the part when I am stumped. When using the combobox1 = textbox1 method, it doesn't work properly (or more so, how I want it to work). It enters the text that is contained in textbox1, but it doesn't show the full line of preemptive text like how it does when typing in the combobox, nor does it then populate the textbox that changes when the combobox changes. If I then click in the combo box and hit the space bar the change event fires and the rest of the preemptive text from the range appears. I tried putting a space " " command at the end combobox1 = textbox1 & " " in the hope it would think there is more text to come but that didn't work. Is there any way to get VBA do do this, or am I asking too much of it?

Hopefully this makes sense.

cheers paul

DisplayName DisplayName · Accepted Answer · 2018-09-15T10:22:46

seems like ComboBox AutoCompletion feature is triggered by UI input only

you can work around it as follows:

Private Sub TextBox1_Change()
    Dim iList As Long
    With Me.ComboBox1
        For iList = 0 To .ListCount - 1
            If Left(.List(iList), Len(Me.TextBox1.Value)) = Me.TextBox1.Value Then
                .ListIndex = iList ' if any combobox1 value matches textbox1 value  then select it
                Exit Sub
            End If
        Next
        .ListIndex = -1 ' if no combobox1 value matches textbox1 value then "deselect" combobox1 
    End With
End Sub

Excel VBA textbox to populate combobox

1 Answers