VBA code: Autocomplete when typing in drop down list Private Sub Worksheet_SelectionChange(ByVal Target As Range)
In the opening Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the worksheet’s Code window. Right click on current sheet tab and click View Code from the context menu. Turn off the Design Mode by clicking Developer > Design Mode. In the Properties dialog box, please replace the original text in the (Name) field with TempCombo.Ħ. Right click it and then select Properties from the right-clicking menu.ĥ. Click Developer > Insert > Combo Box (ActiveX Control).Ĥ. In this Excel Options window, click Customize Ribbon in the left pane, check the Developer box, and then click the OK button. Otherwise, do as follows: Click File > Options to open the Options window. If the Developer tab is showing on your ribbon, shift to step 3. Before inserting a Combo box, you need to add the Developer tab to the Excel ribbon. Open the worksheet that contains the drop down list cell you want to make it autocomplete.Ģ. Firstly, you need to insert a combo box into the worksheet and change its properties.ġ. Please do as follows to make a drop down list autocomplete after typing corresponding letters in the cell.
This tutorial provides two methods to help you achieve it.Īutocomplete when typing in drop down list with VBA codeĪutocomplete when typing in drop down list with an amazing tool Are there any methods to make an auto-completed drop-down list when typing the first letter or any relative characters? That will help people work more efficiently in worksheets with drop-down lists in cells. Enable the macros, when you open the file, if you want to test the drop downs and search strings.How to autocomplete when typing in Excel drop down list?įor a data validation drop-down list with lots of items, you need to scroll up and down in the list to find the one you need or type the whole word into the list box correctly. The zipped file is in xlsm format, and contains macros. On the Sample Files page, go to the Data Validation section, and look for DV0066 – Data Validation – Contains Text
To see how this technique works, you can download the sample file from my Contextures website.
You can visit my Contextures website, for more details on Advanced Filter basics, and how to create complex criteria for an Advanced Filter. The code runs automatically, when you click on a cell with a drop down list. To create the short list, an Advanced Filter extracts products from the main list, based on the criteria that you typed in column B. Or use the “?” wildcard, to represent a single character. For example, type “a*x”, to find any product that contain the letter “a”, then any number of characters, followed by the letter “x”. To give the searches a bit more flexibility, you can use wildcards in the text strings, in column B. If no products have the string of letters, the drop down won’t work.
If you leave the cell in column B empty, the full list of items appears in the drop down. If you type one or more letters in column B, the drop down list in that row will only show the items that contain that string of letters.
In this workbook, there is a data validation drop down in column C, with a list of product names. To help you out in that situation, I’ve uploaded a new sample file. Maybe you know that there are chocolate biscuits in the list, but not the brand name. I’ve previously posted a few variations on a combo box technique, that lets you find an item based on the first few letter that you type.īut that trick won’t help if you don’t know the exact name. If you’ve got a long list of items, it can take a while to find what you’re looking for, in a data validation drop down list.įor example, in the screen shot below, you’d have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don’t pick the Tofu by mistake!)