Universal Searchable dropdown list
VBA

Universal Searchable Drop-down List in Excel

In this article, we have created a universal searchable drop-down list in Excel. It is useful when you have multiple items in your data validation drop-down list. You can use this searchable drop-down to search from multiple drop-down. It is a VBA user form with text box and list box. In the text box you can type your search text and in the list box you will the drop-down items. To update the value on excel sheet form the user form, just double click on the list box item or press enter.

Universal searchable drop-down list in Excel
Universal searchable drop-down list in Excel

You can choose the cell forward direction using the down arrow button on user form. We have given 4 options for direction-

  • Down: You can move down after updating the value form drop-down.
  • Right: You can move right after updating the value form drop-down.
  • None: Selection of active cell will not be changed after updating the value.
  • Close: User form will be closed after updating the value.
Cell forward direction
Cell forward direction

You can move the user form in your personal macro, so that it will working in all the worksheets for any workbook.

  • Just use pick “Frm_Search_List” form using mouse and move the PERSONAL.xlsb.
  • Click to learn how to make PERSONAL.xlsb visible in your VBA editor window.
Move form to PERSONAL.xlsb
Move form to PERSONAL.xlsb
  • Now insert a module in PERSONAL.xlsb. Just go to the insert and insert a module.
  • Put the below code on in this module:
Sub Show_Form()

    Frm_Search_List.Show False

End Sub
Insert module in PERSONAL.xlsb
Insert module in PERSONAL.xlsb

 

  • Go to the Excel on any workbook.
  • Go to the View tab.
  • Go to the Macros and click on View Macros.
  • Macros window will be opened. Choose PERSONAL.xlsb in Macros in drop-down.
Macro Window
Macro Window

 

  • Select the “PERSONAL.XLSB!Show_Form” in Macro Name. and click on Options button.
  • Macro Options window will be opened.
  • Type the “m” in the shortcut key box.
  • Click on OK.
Macro Options window
Macro Options window

Now you can select any cell in you excel sheet where data validation drop-down is available and press “Ctrl+m”. Searchable drop-down form will be popped up.

Learn how to create “Searchable Drop down list for Multiple Cells”

Watch the step by step video tutorial :

Click here to download the practice file.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com