Fix an Excel VBA Searchable Dependent ComboBox
Question details
Make a searchable, dependent Excel VBA ComboBox display all matching items reliably when its source contains more than 11,000 entries.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A user types into a ComboBox whose available items depend on another selection and searches a large source list.
- Observed behavior
- The ComboBox intermittently crashes, and only the first match appears until the user opens the drop-down list.
Identify whether your ComboBox is on a VBA UserForm or a worksheet; the example below is for a UserForm. Check the control names and the worksheet columns that contain the dependent categories and items.
Filter the source array and open the ComboBox list
Use this approach for a UserForm ComboBox that must filter a large dependent list as the user types.
Read the source range into an array, filter it in memory, and assign the matches to the ComboBox in one operation. A guard prevents the list refresh from recursively triggering the Change event.
The example uses a sheet named Data, with categories in column A and items in column B, starting on row 2. It assumes UserForm controls named cboCategory and cboSearch. A blank category searches all categories.
In Excel, press Alt+F11, find your UserForm in Project Explorer, and open its code window. Adapt the sheet and control names in the example to your workbook.
Add the following code to the UserForm module, replacing any existing handlers with the same names: Option Explicit Private mUpdating As Boolean Private Sub UserForm_Initialize() Me.cboSearch.RowSource = "" Me.cboSearch.MatchEntry = fmMatchEntryNone End Sub Private Sub cboSearch_Change() If Not mUpdating Then RefreshMatches True End Sub Private Sub cboCategory_Change() If Not mUpdating Then RefreshMatches False End Sub Private Sub RefreshMatches(ByVal showList As Boolean) Dim ws As Worksheet, data As Variant, result() As Variant Dim lastRow As Long, i As Long, n As Long, j As Long Dim query As String, category As String, item As String Dim previousScreenUpdating As Boolean, errorText As String previousScreenUpdating = Application.ScreenUpdating On Error GoTo Failed mUpdating = True Application.ScreenUpdating = False query = Me.cboSearch.Text If Not IsNull(Me.cboCategory.Value) Then category = CStr(Me.cboCategory.Value) Set ws = ThisWorkbook.Worksheets("Data") lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row If lastRow >= 2 Then data = ws.Range("A2:B" & lastRow).Value2 For i = 1 To UBound(data, 1) item = CStr(data(i, 2)) If (category = "" Or StrComp(CStr(data(i, 1)), category, vbTextCompare) = 0) _ And InStr(1, item, query, vbTextCompare) > 0 Then n = n + 1 Next i If n > 0 Then ReDim result(1 To n, 1 To 1) For i = 1 To UBound(data, 1) item = CStr(data(i, 2)) If (category = "" Or StrComp(CStr(data(i, 1)), category, vbTextCompare) = 0) _ And InStr(1, item, query, vbTextCompare) > 0 Then j = j + 1 result(j, 1) = item End If Next i End If End If Me.cboSearch.Clear If n > 0 Then Me.cboSearch.List = result Me.cboSearch.Text = query Me.cboSearch.SelStart = Len(query) If showList And n > 0 Then Me.cboSearch.DropDown CleanUp: Application.ScreenUpdating = previousScreenUpdating mUpdating = False If errorText <> "" Then MsgBox errorText, vbExclamation, "ComboBox search" Exit Sub Failed: errorText = "Could not refresh the list: " & Err.Description Resume CleanUp End Sub
Run the UserForm, select a category in cboCategory, and type in cboSearch. Confirm that the drop-down opens with all matching items, then test an empty search, no matches, and a large result set.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and move between office suites with minimal disruption; it does not repair Microsoft Excel VBA controls.

Frequently Asked Questions
Why can I see only the first match in the ComboBox?
The matching items may already be in the list, but the drop-down is closed. In the search Change handler, call the UserForm ComboBox's DropDown method after repopulating the list when there are matches.
Why does the Change event run repeatedly when I refresh the list?
Clearing or assigning the ComboBox list can trigger Change again. Use a module-level Boolean guard before refreshing, and reset it in the error-cleanup path.
How do I make the search depend on another ComboBox?
Read the selected value from the category ComboBox and include it in the array filter before assigning matches to the search ComboBox. Refresh the search list from the category control's Change event.
Can I use this code with a worksheet ActiveX ComboBox?
Not without changes. The example uses UserForm controls and their RowSource property. A worksheet ActiveX ComboBox has different references and properties, including ListFillRange, so adapt the event handlers and control references to the worksheet.




