How to Autocomplete Data Validation Drop Down List in Excel

The Data Validation feature offers control over the values that can be input into a cell. Instead of entering values as usual into the cell, values must be selected from a given list. In this article, we will show 2 different methods to autocomplete the data validation drop-down list in Excel, using the following dataset to illustrate our methods.

Method 1 – Using VBA Code in a Combo Box Control to Autocomplete the Data Validation Drop-Down List

We can insert custom VBA code with the ActiveX Control tool to perform data validation from a drop-down list automatically in Excel.

Steps:

First, if you do not see it, add the Developer tab to the ribbon as follows:

• Go to File > Options.
• Choose Customize Ribbon from the Excel Options.
• Tick the Developer option and click OK.

The Developer tab appears on the ribbon.

• Choose Insert from the Developer tab.
• Select Combo Box from the ActiveX Controls.

• Place the Control box on the dataset.
• Right-click the mouse and select Properties from the context menu.

• Change the Name to TempComboBox in the Properties window.

• Go to the Sheet Name field and choose the View Code option from the list.

A VBA Command Module will appear in which we will enter our VBA code.

• Copy the following VBA code and paste it in the module:

Private Sub Wrksht_SelectionChange(ByVal Target As Range)
    Dim combox_1 As OLEObject
    Dim str_1 As String
    Dim ws_1 As Worksheet
    Dim arr_1
    
    Set ws_1 = Application.ActiveSheet
    On Error Resume Next
    Set combox_1 = ws_1.OLEObjects("TempComboBox")
    With combox_1
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        str_1 = Target.Validation.Formula1
        str_1 = Right(str_1, Len(str_1) - 1)
        If str_1 = "" Then Exit Sub
        With combox_1
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = str_1
            If .ListFillRange = "" Then
                arr_1 = Split(str_1, ",")
                Me.TempComboBox.List = arr_1
            End If
            .LinkedCell = Target.Address
        End With
        combox_1.Activate
        Me.TempComboBox.DropDown
    End If
End Sub
Private Sub TempComboBox_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

• Now, save the VBA code and return to the dataset.
• Turn off Design Mode from the Developer tab.

• Select cell C5.
• Select Data Validation from the Data Tools group in the Data tab.

The Data Validation window will appear.

• Choose List in the Allow field.
• In the Source field, select the reference value range.
• Click OK.

• Go to any cell in the Selection column and press any first letter.

As we type a letter, the corresponding suggestions will show in that cell.

• Complete all the cells by selecting names from the suggested list.

Method 2 – Using a Combo Box from ActiveX Controls

In this method we will use only the ActiveX Control for automatic data validation.

Steps:

• Choose the Insert group from the Developer tab.
• Select Combo Box from the ActiveX Controls.

• Place the Combo Box in any blank space in the dataset.
• Right-click and choose Properties from the context menu.

• Put C5 in the Linked Cell field, as the data will be viewed in cell C5.
• Put $B$5:$B$9 in the ListFillRange field.
• Choose 1-fmMatchEntryComplete for the MatchEntry field.
• Save the changes.

• Disable Design Mode from the Developer tab.

• Type any letter in the combo box and suggestions will appear.

The selected data will be displayed in cell C5.

Download Practice Workbook

Related Articles

• How to Create Drop Down List in Multiple Columns in Excel
• Create a Searchable Drop Down List in Excel
• How to Add Blank Option to Drop Down List in Excel
• Creating a Drop Down Filter to Extract Data Based on Selection in Excel
• How to Select from Drop Down and Pull Data from Different Sheet in Excel
• How to Create a Form with Drop Down List in Excel
• How to Remove Used Items from Drop Down List in Excel
• How to Remove Duplicates from Drop Down List in Excel
• How to Fill Drop-Down List Cell in Excel with Color but with No Text
• [Fixed!] Drop Down List Ignore Blank Not Working in Excel
• How to Make Multiple Selection from Drop Down List in Excel
• Hide or Unhide Columns Based on Drop Down List Selection in Excel

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel