VBA Tip: Create a Search Field in VBA

Here's an example of a search field that displays and updates the results as each character is entered in the field (TextBox).

The results can be displayed in different ways (in this example, results are displayed on the sheet and in a ListBox):

search field vba

The VBA code to insert in the sheet that contains the field is as follows:

Option Compare Text

Private Sub TextBox1_Change()
    'Source: https://www.excel-pratique.com/en/vba_tricks/search-field-vba

    Application.ScreenUpdating = False

    Range("A2:A24").Interior.ColorIndex = 2

    If TextBox1 <> "" Then
        For row = 2 To 24
            If Cells(row, 1) Like "*" & TextBox1 & "*" Then
                Cells(row, 1).Interior.Color = RGB(215, 238, 247)
                ListBox1.AddItem Cells(row, 1)
            End If
    End If

End Sub
If needed, you can download the Excel file used here: search-field-vba.xlsm