How to autocomplete when typing in Excel drop down list?
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. In this tutorial, we will provide you with inserting Combo box in the worksheet and running VBA code together for achieving the autocomplete function in the drop down list.
You may interest in:
Recommended Productivity Tools
Firstly you need to insert a combo box into the worksheet and change its properties, and then running the VBA code to enable the autocomplete.
1. Get into the worksheet which contains the drop down list you want it to be autocompleted.
2. Before inserting the Combo box, you need to enable the Developer tab in the ribbon.
1). In Excel 2010 and 2013, click File > Options. And in the Options dialog box, click Customize Ribbon in the right pane, check the Developer box, then click the OK button. See screenshot:
2). In Outlook 2007, click Office button > Excel Options. In the Excel Options dialog box, click Popular in the right bar, then check the Show Developer tabin the Ribbon box, and finally click the OK button.
3. Then click Developer > Insert > Combo Box under ActiveX Controls. See screenshot:
4. Draw the combo box in current opened worksheet and right click it. Select Properties in the right-clicking menu.
5. In the Properties dialog box, you need to:
1). Change the name to TempCombo in the Name field;
2). Specify the font you need in the Font field;
3). Scroll down to select 1-fmMatchEntryComplete in the MatchEntry field;
4). Close the Properties dialog box.
6. Turn off the Design Mode with clicking Developer > Design Mode.
7. Right click on the current opened worksheet tab and click View Code. See screenshot:
8. Make sure that the current worksheet code editor is opened, and then copy and paste the below VBA code into it. See screenshot:
VBA code: Autocomplete when typing in drop down list
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Update by Extendoffice: 2017/8/15 Dim xCombox As OLEObject Dim xStr As String Dim xWs As Worksheet Set xWs = Application.ActiveSheet On Error Resume Next Set xCombox = xWs.OLEObjects("TempCombo") With xCombox .ListFillRange = "" .LinkedCell = "" .Visible = False End With If Target.Validation.Type = 3 Then Target.Validation.InCellDropdown = False Cancel = True xStr = Target.Validation.Formula1 xStr = Right(xStr, Len(xStr) - 1) If xStr = "" Then Exit Sub With xCombox .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = xStr .LinkedCell = Target.Address End With xCombox.Activate Me.TempCombo.DropDown End If End Sub Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode Case 9 Application.ActiveCell.Offset(0, 1).Activate Case 13 Application.ActiveCell.Offset(1, 0).Activate End Select End Sub
9. Click File > Close and Return to Microsoft Excel to close the Microsoft Visual Basic for Application window.
10. Now, just click the cell with drop down list, you can see the drop-down list is displayed as a combo box, then type the first letter into the box, the corresponding word will be completed automatically. See screenshot:
Note: This VBA code is not applied to merged cells.
- How to auto populate other cells when selecting values in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 days agoThanks for this great code. I do have a question about if it would be possible to change the color of the linked cell if the person chooses a value from the list or if they type one of their own? For example, if I choose a value from the list the linked cell would show green text when I left the cell. If I typed my own value, then the linked cell would show red indicating I didn't choose one of the values from the list. Is this possible?
To post as a guest, your comment is unpublished.· 8 days agoThanks for the wonderful code.
I have a question.
My cells in excel are of the nature x4x - y4y. The answers from the drop down are filtered only for the words matching the first half or starting with the alphabet xx but if i search for y4y, it will not show in the results.
Is there a way to include the second half in the search as well?
How can i modify the code so that the results shows all the alphabets from the search menu?
Ex: If am searching for the word "example", but i input "ample", I would like to have the word "example" shown in the list as it contains the part of the search request.
To post as a guest, your comment is unpublished.· 14 days agothanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution? Thanks in advance. ;)
To post as a guest, your comment is unpublished.· 14 days agothanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution. Thanks in advance. ;)
To post as a guest, your comment is unpublished.· 1 months agoWhen I copy the VBA code into Visal Basic I cannot use copy paste anymore. I have to start Excel in normal mode to be able to copy,. How can I solve this?
- ← Previous
- Next →