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

or

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


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. Download the full feature 60-day free trail of Kutools for Excel now!

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. 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: 2018/9/21
    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

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.

Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel

Related articles:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    RK · 1 months ago
    How can I set it to accept only the text in the list?
    • To post as a guest, your comment is unpublished.
      crystal · 20 days ago
      Hi RK,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Anthony · 2 months ago
    How can the code be modified such that pressing SHIFT + TAB moves the cursor left in the row?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Anthony,
      Sorry can't help you with that. Thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Anthony · 1 months ago
        No problem. I figured it out. Thanks, Crystal.
  • To post as a guest, your comment is unpublished.
    Parth · 3 months ago
    But what if i have dynamic list ? How can i provide the input to the combobox and refresh everytime the data is been added?
    • To post as a guest, your comment is unpublished.
      Conor · 26 days ago
      You'll have to make a table of the dynamic dataset and assign it to the 'ListFillRange' within the properties of your combobox.
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Parth,
      Sorry can’t help you with this. Welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    MR Excel · 4 months ago
    Anyone looking for a simple dropdown input form in excell based on a list; i used Data.. "Data Validation" and then choose Allow: "List" and pointed the source at the list.
    This does not do the auto complete but does avoid any macros or dev and just uses native simple Excel features.
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Good day,
      The dropdown list mentioned in the post is the Data Validation dropdown list.
      After creating the Data Validation dropdown list in the worksheet, go ahead to apply the steps from 2 to 10 in the post.
  • To post as a guest, your comment is unpublished.
    Fabian · 4 months ago
    Buenas tardes dentro de esa lista predictiva tengo campos de lista con la función indirecto pero me elimina , como puedo conservar las funciones de lista conservando la búsqueda predictiva.