Autocomplete Data Validation Drop Down List in Excel (2 Methods)

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.

2 Methods to Autocomplete Data Validation Drop-Down List in Excel

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. Autocomplete Data Validation Drop-Down List Using VBA Codes in Combo Box Control

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.

Read More: Data Validation Drop Down List with VBA in Excel (7 Applications)

2. Autocomplete 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.

Read More: How to Create Excel Drop Down List for Data Validation (8 Ways)

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. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Make a Data Validation List from Table in Excel (3 Methods)
• Create Data Validation Drop-Down List with Multiple Selection in Excel
• How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)
• Excel Data Validation Based on Another Cell Value
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
• Excel Data Validation Alphanumeric Only (Using Custom Formula)