NOTE: Other languages are Google-Translated. You can go to the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

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.

Autocomplete when typing in drop down list with VBA code


You may interest in:

Combine multiple worksheets/workbooks into one worksheet / workbook:

Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Read More      Download the free trial now



Autocomplete when typing in drop down list with VBA code

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.


Related articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Leandro · 14 hours ago
    Hay alguna forma de mover el Combo? Como se encuentra directamente abajo de la lista desplegable al apretar Enter (una vez seleccionado el dato de dicha lista) te selecciona el Combo y termina mostrándote dos listas.
  • To post as a guest, your comment is unpublished.
    Alicia · 9 days ago
    This code seems to work sometimes for me - but not consistently. I have a document with multiple lists and it will auto populate most of the time but not all of the time. It seems to have a problem auto populating when there was no information in the cell prior - however data validation extends the entire column. Please advise a fix.
  • To post as a guest, your comment is unpublished.
    Martin Winlow · 18 days ago
    Hi,


    Could you please re-do this tutorial for Excel for Mac V15? MW
  • To post as a guest, your comment is unpublished.
    Robert Lonergan, Jr. · 23 days ago
    I am using this script which works well with text but not with numerals. Here are some screenshots; 1 is the code, 2 is working by alpha, 3 is not working by Number. Ultimately I would like to be able to search by name or number (Col A or Col C). To work around the separate column issue, I copied the same date and transposed the data in those cells.

    Any help would be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 21 days ago
      Dear Robert,
      What do you mean "is not working by number"? The code works well with text and numerals in my case. And I saw the case in your picture 3, the related whole number 40726 is automatically populated when you type 407 into the list box. Please let me know if I missed something in the case.
  • To post as a guest, your comment is unpublished.
    Brittany · 27 days ago
    Hello, Is there anyway to make the drop down menu only show results that match what you're typing? For example; I am using this to select items for an invoice template and I have a dew wines that start with 'Gaja'. As I type Gaja the top result shows in my list of 20 results but the other options are below it and I have to use the mouse to scroll down to those or type the name of the wine until it is the only option. I would like to type 'G' and then be only shown all items with G. Then type 'Ga' and only see items with GA and so on.