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.
- 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.
- Choose Insert from the Developer tab.
- Now, select Combo Box from the ActiveX Control.
- Put the Control box on the dataset.
- Click the right button of the mouse and select Properties from the list.
- Change the Name to TempComboBox from the Properties window.
- 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.
- 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
- Now, save the VBA code and go to the dataset. Turn off the Design Mode from the Developer tab.
- Select Cell C5.
- Select the Data Tools group from the Data tab.
- Choose Data Validation from the list.
- Data Validation window will appear. Choose List in the Allow field.
- In the Source field choose the reference value range.
- Then press OK.
- 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.
- Choose Insert group from the Developer tab.
- Select Combo Box from the ActiveX Control.
- Place the Combo Box on any blank space of the dataset.
- Then, press the right button of the mouse.
- Choose Properties from the list.
- 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.
- Now, disable the Design Mode from the Developer tab.
- 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.
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.
- 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