Creating a dropdown list with a search feature in Excel VBA.

The goal is to create a dynamic dropdown in a cell and allow the user to search through the options by typing into the cell.

Step 1: Prepare the Data

We begin by creating a list of items in a specific column (e.g., Column A). The data validation dropdown will be based on this list.

Step 2: Create the Dropdown with Search Feature

The following VBA code adds data validation with a search function. The search will be performed as the user starts typing, and matching items will appear in the dropdown list.

VBA Code:

Sub CreateDropdownWithSearch()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim listRange As Range
    Dim listName As String
    Dim validationFormula As String
    Dim lastRow As Long   
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range for the list to be used in the dropdown
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set listRange = ws.Range("A1:A" & lastRow)   
    ' Name for the list range
    listName = "DropdownListSearch"   
    ' Create a named range for the list
    ws.Names.Add Name:=listName, RefersTo:=listRange   
    ' Apply data validation to cell B1
    Set rng = ws.Range("B1")
    rng.Validation.Delete ' Delete any existing validation   
    ' Add data validation for the dropdown
    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=" & listName   
    ' Create a search event (Ctrl + L) to filter the list
    Application.OnKey "^l", "FilterList" ' Ctrl+L to trigger search   
End Sub
Sub FilterList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim searchTerm As String
    Dim filteredRange As Range
    Dim cell As Range   
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the data range
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)   
    ' Prompt the user to enter a search term
    searchTerm = InputBox("Enter a search term to filter the list:")
    ' Apply filter based on the search term
    rng.AutoFilter Field:=1, Criteria1:="*" & searchTerm & "*"   
    ' Reset the data validation to show only the filtered results
    Set filteredRange = rng.SpecialCells(xlCellTypeVisible)
    rng.Validation.Delete
    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=" & filteredRange.Address
End Sub

Explanation of the Code:

CreateDropdownWithSearch:

• Define the Worksheet and Data Range:
  • We define the worksheet (ws) and the data range (listRange) that will be used for the dropdown list (Column A in this case).
• Create a Named Range for the List:
  • We create a named range (DropdownListSearch) that refers to the data range.
• Apply Data Validation to the Cell:
  • We apply data validation to cell B1, specifying the named range (DropdownListSearch) as the source for the dropdown list.
• Add a Keyboard Shortcut for Search:
  • We assign a keyboard shortcut (Ctrl+L) using OnKey to trigger the search function when pressed.

FilterList:

• Define the Worksheet and Data Range:
  • We access the worksheet and the data range that contains the items for the dropdown.
• Prompt the User for a Search Term:
  • An InputBox is used to ask the user to enter a search term, which will be used to filter the list.
• Apply the Filter:
  • We apply an AutoFilter to the range using the search term as a filter criterion (it looks for items that contain the search term).
• Reset the Data Validation:
  • After applying the filter, we reset the data validation to show only the filtered results in the dropdown list.

Step 3: Using the Code

• Run the Code:
  • Open the VBA editor (Alt + F11), paste this code into a module, and then run the CreateDropdownWithSearch macro.
• Search in the List:
  • Select cell B1, start typing to show the dropdown, and use the Ctrl+L shortcut to filter the items based on a search term.

Conclusion

This code enables the creation of a dynamic dropdown list in Excel with a search feature using VBA. The user can easily search for an item by filtering the list as they type, making it easier to find and select items from large datasets.