How to Autocomplete Data Validation Drop Down List in Excel

Data validation is an interesting feature of Excel. This feature offers control to the user to input values in a cell. Users can not input whatever they want. They have to select from a given list. We will discuss how to perform autocomplete data validation drop-down list in Excel.

Here, we will show 2 different methods to autocomplete the data validation drop-down list in Excel. We will consider the following dataset for autocomplete data validation.

1. Using Excel VBA Codes in Combo Box Control to Autocomplete Data Validation Drop-Down List

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

Step 1:

• First, we have to add the Developer tab to the ribbon. Go to File > Options.
• Choose Customize Ribbon option from Excel Options.
• Tick the Developer option and press OK.

Step 2:

• Choose Insert from the Developer tab.
• Now, select Combo Box from the ActiveX Control.

Step 3:

• Put the Control box on the dataset.
• Click the right button of the mouse and select Properties from the list.

Step 4:

• Change the Name to TempComboBox from the Properties window.

Step 5:

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

Now, a VBA Command Module will appear. We have to put VBA code on that module.

Step 6:

• Copy and paste the following VBA code on 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

Step 7:

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

Step 8:

• Select Cell C5.
• Select the Data Tools group from the Data tab.
• Choose Data Validation from the list.

Step 9:

• Data Validation window will appear. Choose List in the Allow field.
• In the Source field choose the reference value range.
• Then press OK.

Step 10:

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

As we put a letter, the corresponding suggestion will show on that cell.

Now, complete all the cells by our desired selection from the suggested list.

2. Autocompleting Data Validation Drop-Down List with a Combo Box from ActiveX Controls

We will use only the ActiveX Control for automatic data validation.

Step 1:

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

Step 2:

• Place the Combo Box on any blank space of the dataset.
• Then, press the right button of the mouse.
• Choose Properties from the list.

Step 3:

• Now, put C5 in the Linked Cell field, as the data will view on Cell C5.
• Put $B$5:$B$9 on ListFillRange field.
• Choose 1-fmMatchEntryComplete for the MatchEntry field and save the changes.

Step 4:

• Now, disable the Design Mode from the Developer tab.

Step 5:

• Now, put any letter on the combo box and the suggestion will appear. And finally, data will be viewed on Cell C5.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we performed data validation from the dropdown list. We added autocompletion of data validation from the drop-down list of Excel. I hope this will satisfy your needs. You can give your feedback in the comment box.

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