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):

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
    ListBox1.Clear

    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
        Next
    End If

End Sub