VBA & Macro Problems

Fix an Excel VBA Searchable Dependent ComboBox

Khadija KhanKhadija Khan Nov 22, 2026 868 views

Question details

Make a searchable, dependent Excel VBA ComboBox display all matching items reliably when its source contains more than 11,000 entries.

Fix an Excel VBA Searchable Dependent ComboBox
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the UserForm code

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.

2
Replace the search event logic

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

3
Test the dependent search

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.

Adapt existing event handlers: Merge this logic with any existing initialization or Change procedures rather than creating duplicate procedures. The example retains the typed text without automatically selecting the first match; the user can select an item from the open list.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceCompatible with Microsoft Excel formats including XLSX and XLSUse spreadsheet filters to explore large lists without relying on a searchable VBA ComboBoxFree and lightweight for everyday office work
QA img-9

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.