Searchable drop down list in excel using vba
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. Show
You can choose the cell forward direction using the down arrow button on user form. We have given 4 options for direction-
You can move the user form in your personal macro, so that it will working in all the worksheets for any workbook.
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.
A drop-down list means that one cell includes several values. When the user clicks the arrow on the right, a certain scroll appears. He can choose a specific one. A drop-down list is a very handy Excel tool for checking the entered data. The following features of drop-down lists allow you to increase the convenience of data handling: data substitution, displaying data from another sheet or file, the presence of the search and dependency function. Path: the «DATA» menu – the «Data Validation» tool – the «Settings» tab. The data type – «List». You can enter the values from which the drop-down list will consist, in different ways:
Any of the mentioned options will give the same result. It is necessary to make a drop-down list with values from the dynamic range. If changes are made to the available range (data are added or deleted), they are automatically reflected in the drop-down list.
Let’s test it. Here is our table with a list on one sheet: Add the new value «Spruce» to the table. Now delete the «Birch» value. The "smart table", which easily "expands" and changes, has helped us to perform our task. Now let's make it possible to enter new values directly into the cell with this list and have data automatically added to the range.
When you enter a new name in the empty cell of the drop-down list, the following message will appear: «Add entered name Baobab?». Click «OK» and one more row with the «Baobab» value will be added. Excel drop-down list with data from another sheet / fileWhen the values for the drop-down list are located on another sheet or in another workbook, the standard method does not work. You can solve the problem with the help of the =INDIRECT() function: it will form the correct link to an external source of information.
The name of the file from which the information for the list is taken is enclosed in square brackets. This file must be opened. If the book with the desired values is stored in a different folder, you need to specify the path completely. How to create dependent drop-down listsTake three named ranges: It is an indispensable prerequisite. Above you can see how to turn a normal scroll in a named range (using the «Name Manager»). Remember that the name cannot contain spaces or punctuation.
Selecting multiple values from a drop-down list in ExcelSometimes, you need to select several items from the drop-down list. Let's consider the ways of performing this task.
Do not forget to change the ranges to "your own" ones. Create scroll in the classical way. The rest of the work will be done by macros. Searchable drop-down list in Excel
Download drop-down lists example When you enter the first letters from the keyboard, the appropriate items are displayed. These are not all the pleasant moments of this instrument. Here you can customize the visual representation of information, specify two columns at once as a source. |