How to autocomplete when typing in Excel drop down list?

For a data validation drop-down list with many 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. Is there any way to make the drop-down list auto-complete when typing the corresponding characters? This would help people work more efficiently in worksheets with drop-down lists in cells. This tutorial provides two methods to help you achieve it.

Make drop down lists autocomplete with VBA code
Easily make drop-down lists autocomplete in 2 seconds
More tutorials for drop down list...

Make drop down lists autocomplete with VBA code

Please do as follows to make a drop down list autocomplete after typing corresponding letters in the cell.

Firstly, you need to insert a combo box into the worksheet and change its properties.

1. Open the worksheet that contains the drop down list cells you want to make them autocomplete.
2. Before inserting a Combo box, you need to add the Developer tab to the Excel ribbon. If the Developer tab is showing on your ribbon, shift to step 3. Otherwise, do as follows to get the Develper tab show up in the ribbon: Click "File" > "Options" to open the "Options" window. In this "Excel Options" window, click "Customize Ribbon" in the left pane, check the "Developer" box, and then click the "OK" button. See screenshot:
   
3. Click "Developer" > "Insert" > "Combo Box (ActiveX Control)".
   
4. Draw a combo box in current worksheet. Right click it and then select "Properties" from the right-clicking menu.
   
5. In the "Properties" dialog box, please replace the original text in the "(Name)" field with "TempCombo."
   
6. Turn off the "Design Mode" by clicking "Developer" > "Design Mode."

Then, apply the below VBA code

1. Right click on current sheet tab and click "View Code" from the context menu. See screenshot:
   
2. In the opening "Microsoft Visual Basic for Applications" window, please copy and paste the below VBA code into the worksheet’s Code window.
   VBA code: Autocomplete when typing in drop down list

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Update by Extendoffice: 2020/01/16
       Dim xCombox As OLEObject
       Dim xStr As String
       Dim xWs As Worksheet
       Dim xArr
       
       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
               If .ListFillRange = "" Then
                   xArr = Split(xStr, ",")
                   Me.TempCombo.List = xArr
               End If
               .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

   
3. Press "Alt + Q" keys simultaneously to close the Microsoft Visual Basic Applications window.

From now on, when click on a drop down list cell, the drop down list will prompt automatically. You can start to type in the letter to make the corresponding item complete automatically in selected cell. See screenshot:

Easily make drop-down list autocomplete in 2 seconds

For most Excel users, the above VBA method is hard to master. But with the "Searchable Drop-down List" feature of Kutools for Excel, you can easily enable autocomplete for data validation drop-down lists in a specified range in just 2 seconds. What’s more, this feature is available for all Excel versions.

1. To enable autocomplete in your dropdown lists, first select the range with the dropdowns. Then, navigate to the "Kutools" tab, choose "Drop-down List" > "Make Drop-down List Searchable, Auto-popup."
2. In the "Make the Drop-down List Searchable" dialog box, click the "OK" button to save the setting.

Result

Once the configuration is complete, clicking on a drop-down list cell within the specified range will bring up a list box. When entering characters, as long as one item matches exactly, the entire word is immediately highlighted in the list box and can be populated into the drop-down list cell simply by pressing the Enter key.

Related articles: