• Developer For Excel 2016 Autocomplete Drop Down List
• Developer For Excel 2016 Autocomplete Drop Down Menu

Now we create the dynamic drop down list in cell D9, follow the steps given below:. Select the cell D9. Open Data Validation dialog box, by pressing the key ALT+D+L. In Allow drop down list, select List. And then enter this function =INDIRECT (“Table1”) in source tab. Note: - When we click on OK, in Excel, window pops up. Re: Auto complete text in a drop down list cell. Excel doesn't allows the drop down to Autocomplete, generally we use for Data Validation. Therefore you have to work with Combo box, is one of the Active X controls. Please check this link, I've shown how to do it all about Autocomplete. Mar 09 2021 01:47 AM. Double-click on one of the cells that contains a data validation list. The combo box will appear. Select an item from the combo box dropdown list. Click on a different cell, to select it. The selected item appears in previous cell, and the combo box disappears.

To create your own drop-down list for the cell, do the following:

1. Create a column or a row of the items in theactive spreadsheet for the drop-down list.

2. Select the cell that needs a drop-down list fordata validation (cell B2, in this example).

3. On the Data tab, in the Data Toolsgroup, click Data Validation:

4. In the Data Validation dialog box, onthe Settings tab:

• In the Allow drop-down list, select the List item.
• In the Source box, specify the range that contains the drop-down list items(in this example, the range is E1:E8).
• Check In-Cell Dropdown option to show the drop-down list. Otherwise, Excelvalidates your data entry but does not show the drop-down button to select itemsfrom the list.

5. Click OK.

Notes:

• You can hide the range of values used in the Source box or create it onthe protected sheet that you can hide.
• You can add the drop-down list to multiple cells by selecting the appropriatecells or copy/paste the cell with a list.
• If the list is short, you can avoid Step 1. Instead, type your list items(separated by commas) in the Source box in the Data Validationdialog box:
• If you plan to share your workbook with users of Excel 2007 or earlier, makesure that the list is on the same sheet as the drop-down list. Alternatively,you can use a named range.

See also this tip in French:Création d’une liste déroulante dans une cellule.

A combo box is a drop-down list from where a user can select an item or fill in his/her own choice. To create a combo box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Combo Box.

3. Drag a combo box on your worksheet.

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ComboBox1 as the name of the combo box.

Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

Developer For Excel 2016 Autocomplete Drop Down List

4. Open the Visual Basic Editor.

Developer For Excel 2016 Autocomplete Drop Down Menu

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

7. To add items to the combo box, add the following code lines to the Workbook Open Event:

Note: use Sheet2 if your combo box is located on the second worksheet, Sheet3 if your combo box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the code lines below before these code lines. The first code line clears the combo box. This way your items won't be added multiple times if you execute your code more than once. The second code line clears your own choice.

8. To link this combo box to a cell, right click on the combo box (make sure design mode is selected) and click on Properties. Fill in D2 for LinkedCell.

Note: also see the ListFillRange property to fill a combo box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

Although in some situations it can be useful to directly place a combo box on your worksheet, a combo box is particularly useful when placed on a Userform.