How to populate combobox values from textbox text in excel vba form

Question

I have a problem in my VBA form in Excel and I'm trying to filter the value of a combobox using VLOOKUP from whatever I type in the textbox. How do I achieve this?

My code is:

Private Sub btnTemplateSearch_Click()
Dim filterInfo As Range
Set filterInfo = Worksheets("InfoDump").Range("E2:F46")
txtTemplateFilter.Text = filterInfo.Columns(2).Value

Me.cboTemplateType.List = Application.WorksheetFunction.VLookup(Me.txtTemplateFilter.Text, filterInfo,2,True)

You'll have to clarify your request. Do you want to filter the values that are in your `combobox` by typing a value into a textbox? Or do you want to use the value of your textbox to perform a `Vlookup` against a worksheet? I think an example of what you want to do and your expected outcome might help. — basodre, Oct 12 '20 at 16:33
Yeah sorry about that. I was hoping to filter the values inside my combobox by using a word or part of the word typed inside of the textbox. Like instead of scrolling through entries in the combobox i will just type apple and the combobox will populate all items in my worksheet column that has the word apple in it. — Tee Jay, Oct 12 '20 at 18:06
A ComboBox can't filter. You'll have to reduce the input list based on your filter before you load it into the ComboBox — chris neilsen, Oct 12 '20 at 18:32
Its supposed to look up all the entries that match a word in the textbox when i click on my command button. That way it the combobox will just load all the matching information based on the textbox text value — Tee Jay, Oct 12 '20 at 18:55

score 1 · Answer 1 · answered Oct 12 '20 at 19:16

Below is an example of a block of code that can be used to filter the list of ComboBox entries. I decided to Dim an array variable ListForComboBox at the module level such that all procedures in the module can access it. It gets populated at the form's init event by calling LoadListForComboboxArray. You can make changes to this procedure to update the range of input values or any other changes. The combobox's list property is set to this array.

Here's the caveat: you may want to consider tuning the below for performance. I included a textbox, and in the change event, I make a call to set the global array to a filtered version of the original array, based on textbox value. That means that the code will filter the array everytime you type a letter (so if you type "ABC", it will filter three times, creating a new array each time). You may want to assign that function to a different event (textbox exit, maybe) such that the code only fires once you leave the text box, and only once.

Let me know if you have trouble adapting the code:

Dim ListForCombobox() As String

Private Sub TextBox1_Change()
    Me.ComboBox1.List = Filter(ListForCombobox, Me.TextBox1.Value)
    Debug.Print "CALLED"
End Sub

Private Sub UserForm_Initialize()
    LoadListForComboboxArray
    
    Me.ComboBox1.List = ListForCombobox
End Sub

Private Sub LoadListForComboboxArray()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("A1:A11")
    
    ReDim ListForCombobox(1 To rng.Rows.Count)
    
    For i = 1 To rng.Rows.Count
        ListForCombobox(i) = rng(i).Value
    Next i
    
    Debug.Print ListForCombobox(1)
End Sub

Assigning range values to a datafield array needs to check for single cell (or row values). - FYI you might profit from reading [how to speed up filling/filtering of listbox values on UserForm](https://stackoverflow.com/questions/46983374/how-to-speed-up-filling-of-listbox-values-on-userform-excel/47003993#47003993) - @basodre — T.M., Oct 13 '20 at 15:22
@T.M. Thanks for the link. It included some interesting reading. One of the benefits of the approach that I used above is that it only needs to load the data into an array a single time, and then the filter is done in-memory. I sped up load time by using the `Transpose` function. I didn't do the first time because `Transpose` has a limit of 65,536 rows. I will note: I tested my function with max 65k rows, and the average time to filter the combobox was about 0.015 seconds per filter . Worth noting that this approach only works in 1-d arrays. — basodre, Oct 13 '20 at 16:04

How to populate combobox values from textbox text in excel vba form

1 Answers1