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