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.


Download Practice Workbook

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


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.

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

Step 2:

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

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

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.

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

Step 5:

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

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

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.

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

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.

Autocomplete Data Validation Drop Down List from Using ActiveX Controls and Excel VBA

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.

Autocomplete Data Validation Drop Down List from Using Only ActiveX Controls

Step 4:

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

Autocomplete Data Validation Drop Down List from Using Only ActiveX Controls

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

2 Comments
  1. I am confused. I have a Checklist worksheet with Officer Names. I have a Data Sheet worksheet that includes my data tables for choices of meat and sides. Where am I supposed to put the combobox, Checklist or Data Sheet? Am I supposed to put a combobox next to each data table, tblMeats & tblSides? Then go to the Checklist sheet and create a drop-down list under Meats1, Meats2, Sides1, Sides2? Or am I supposed to create the combobox on top of the tblMeats and tblSides?

    How do I put the list range in the properties “ListFillRange”?

Leave a reply

ExcelDemy
Logo