Searchable Dropdown in Excel UserForms: How It Works

Yes. In an Excel VBA UserForm, a ComboBox can provide a searchable dropdown: its built-in autocomplete can complete a matching entry, or VBA can filter the available choices as the user types. For a true search-as-you-type list, use a ComboBox with a small Change-event routine; Excel’s worksheet AutoFilter is not required.

What a Searchable UserForm Dropdown Means

A UserForm is a custom dialog box built with VBA, and a ComboBox is the control that combines a text field with a dropdown list. A searchable dropdown lets someone find an item in a long list by typing rather than scrolling through every entry. For example, a form used to record an order might offer hundreds of product names; typing a few letters can narrow the visible choices to relevant products.

There are two different behaviors that people often call searchable. Autocomplete keeps the full list and completes the text when it finds a matching entry. Live filtering changes the displayed list as the user types, often showing any entry containing the typed characters. The distinction matters: autocomplete is simpler, while filtering is more flexible for long or irregular lists.

This guidance is for desktop Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros or UserForms. Excel for Mac has VBA and UserForms, but control availability and behavior can differ, so Windows-specific steps and control behavior should be checked in the Mac edition before relying on them in a shared workbook.

What the ComboBox Can Do by Itself

The ComboBox has a MatchEntry property that can match typed characters against its list. Set it to fmMatchEntryComplete to enable completion, and set Style to fmStyleDropDownCombo if users should be able to type as well as choose from the list. This is useful when a list is manageable and the first matching text is enough to help people locate an item.

Autocomplete is not the same as a search engine. It generally helps complete a value that begins with the characters entered; it does not reliably find a name because a word in the middle matches. For instance, typing Mar may help locate Market Street, but it will not necessarily find North Market simply because Market appears later in the name. If users expect that kind of search, filter the list with VBA instead.

To add the control, open the VBA editor from Developer > Visual Basic, choose Insert > UserForm, and add a ComboBox from the Toolbox. If the Developer tab is hidden in Windows Excel, turn it on through File > Options > Customize Ribbon. In the Properties window, give the control a clear name such as cboSearch. The code below assumes a worksheet named Lists and entries in column A beginning at A2.

Build a Live-Filtering Dropdown

In the UserForm code module, load the source values when the form opens, then respond to the ComboBox Change event. The example searches for the typed text anywhere in each item, without regard to capitalization. It skips blank cells and error values, and it preserves the text and caret position while rebuilding the list.

Option Explicit

Private mItems As Collection
Private mUpdating As Boolean

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim itemText As String

    Set ws = ThisWorkbook.Worksheets("Lists")
    Set mItems = New Collection
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    mUpdating = True
    With Me.cboSearch
        .Style = fmStyleDropDownCombo
        .MatchEntry = fmMatchEntryNone
        .Clear
    End With

    If lastRow >= 2 Then
        For Each cell In ws.Range("A2:A" & lastRow)
            If Not IsError(cell.Value2) Then
                itemText = Trim$(CStr(cell.Value2))
                If Len(itemText) > 0 Then
                    mItems.Add itemText
                    Me.cboSearch.AddItem itemText
                End If
            End If
        Next cell
    End If
    mUpdating = False
End Sub

Private Sub cboSearch_Change()
    Dim query As String
    Dim candidate As Variant
    Dim caretPos As Long

    If mUpdating Then Exit Sub
    If mItems Is Nothing Then Exit Sub

    query = Me.cboSearch.Text
    caretPos = Me.cboSearch.SelStart
    mUpdating = True
    On Error GoTo CleanUp

    With Me.cboSearch
        .Clear
        For Each candidate In mItems
            If Len(query) = 0 Or InStr(1, CStr(candidate), query, vbTextCompare) > 0 Then
                .AddItem CStr(candidate)
            End If
        Next candidate
        .Text = query
        If caretPos > Len(query) Then caretPos = Len(query)
        .SelStart = caretPos
        If .ListCount > 0 Then .DropDown
    End With

CleanUp:
    mUpdating = False
End Sub

The mItems collection retains the original choices so that each keystroke filters the complete source, not the already-filtered results. The mUpdating flag prevents changes made by the code itself, such as clearing and repopulating the control, from recursively starting another filter operation. Without that guard, the event can repeatedly call itself or produce unpredictable results.

To search only from the beginning of each item, replace the InStr condition with a prefix comparison. For example, use StrComp(Left$(CStr(candidate), Len(query)), query, vbTextCompare) = 0 as the test when the query is not empty, and include every candidate when it is empty. Prefix matching is familiar for names and codes; substring matching is more forgiving when users remember only a word in the middle.

Choosing the Right Search Behavior

Choose the simplest behavior that fits how people recognize the entries. A built-in autocomplete setting has less code to maintain and is often a good choice for short lists. Live filtering is more helpful when the list is long, users may know only part of a label, or scrolling would be cumbersome. Other useful decisions include whether matching should ignore case, whether spaces should be trimmed, and whether results should appear in the same order as the source.

Use autocomplete when users usually know the beginning of the item and the list is short enough to browse.
Use prefix filtering when entries have consistent names or codes and users normally start with the first character.
Use substring filtering when a memorable word may occur anywhere in a longer label.
Use a separate search box and list when the interface needs richer results, extra columns, or a clear no-results message.

The example keeps the source order. If the source is not sorted, results will not automatically be alphabetical. Sort the source data or sort the values before adding them if alphabetical results are important. Also consider whether duplicates are meaningful: the sample keeps duplicates, so repeated source entries will appear repeatedly.

Load and Validate the Source Data

A search control is only as dependable as its source list. Keep choices in a dedicated worksheet range, use one consistent value per row, and avoid mixing headings, notes, and data in the same column. The code expects the sheet name Lists and reads from A2 down to the last used cell in column A. Change those references to match the workbook, and ensure the worksheet exists with that exact name.

The ComboBox lets users type text that is not in its choices because its style is set to fmStyleDropDownCombo. That can be desirable for flexible entry, but it is risky if the form must save only approved values. Before writing the selection to a worksheet or database, check that the value is present in the allowed list. A simple practical approach is to compare the submitted value with the source collection and show a message if no exact match exists. Searching and validation solve different problems: filtering helps locate a value; validation controls what the form accepts.

For a list that changes while the form is open, decide whether the form should refresh the source each time it opens or immediately when the source changes. Loading on initialization is usually sufficient for ordinary forms. If another procedure modifies the list during the open session, add a deliberate refresh routine rather than silently rebuilding the collection on every keystroke.

Common Problems and Practical Fixes

If typing causes the list to flicker, the ComboBox may be rebuilding itself without an event guard. Keep the mUpdating check and reset the flag even if an error occurs, as the CleanUp section does. If no matches appear, confirm that the source range contains text, the worksheet and control names are spelled correctly, and the query is being compared against the original collection.

The control will not accept typing: check that its Style is fmStyleDropDownCombo rather than fmStyleDropDownList.
The list never narrows: confirm that the code is in the UserForm module and that the event procedure name matches the control name, such as cboSearch_Change.
Some entries are missing: inspect the source column for blanks, formula errors, unexpected data, or values placed outside the range the code reads.
The dropdown closes or behaves oddly: check whether other event code is changing the control, and consider removing the automatic .DropDown call if reopening the list on every keystroke is distracting.
A typed value is saved even though it is not an option: add an exact-match validation step before the form submits it.

The sample opens the dropdown whenever at least one result remains. This makes the filtered choices visible while typing, but it may feel intrusive in a compact form. Removing the .DropDown line leaves the filtered list available when the user opens the arrow. For lists with thousands of records, rebuilding every result on every keystroke can also become slow; limit the source range, wait until a minimum number of characters is entered, or use a more efficient data structure and a separate results control.

Alternatives and Workbook Compatibility

A worksheet Data Validation dropdown is suitable for restricting cell entry, but it is not a UserForm ComboBox and does not provide the same customizable live-search behavior. A ListBox can display several results at once and is useful when users need to scan choices, while a TextBox paired with a ListBox offers more control over search and display. Those designs require additional event code but can show extra fields such as a product code beside a name.

Save a workbook containing this VBA as an Excel Macro-Enabled Workbook (.xlsm), and make sure users open it in desktop Excel with macros permitted under their organization’s security settings. Macros cannot be undone with Ctrl+Z, so save a separate copy of the workbook before running or changing macro code. VBA UserForms are not available in Excel for the web; for a browser-based workflow, consider an appropriate web form or another supported application instead.

Frequently Asked Questions

Can an Excel UserForm ComboBox search as I type?
Yes. Built-in MatchEntry autocomplete can help complete a matching entry. For a list that actively filters to matching results as the user types, use a ComboBox Change event like the example above.

How do I make the dropdown search anywhere in an item?
Use InStr with vbTextCompare to look for the query anywhere in each item. A prefix check searches only at the beginning, so choose the behavior that matches how users will look up entries.

Can users type a value that is not in the dropdown?
Yes, when the ComboBox Style is fmStyleDropDownCombo. If only listed values are allowed, validate the final value against the source list before saving it.

Why does my filtered list show no results?
Check the source sheet and range, the UserForm and ComboBox names, and whether the event code is in the form’s code module. Also verify that the search condition matches the behavior you want, such as prefix rather than substring matching.

Will a VBA UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms. Users need a compatible desktop Excel installation, or the workflow needs a web-based alternative.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.