Home>Blogs>VBA>Universal Searchable Drop-down List in Excel
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
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”

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial :

 

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com