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.
- 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.
- 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)
- How to Edit AutoComplete in Excel (4 Easy Methods)
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.
- 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.
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.
- How to Populate a List Based on Cell Value in Excel
- Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
- How to Create Excel Drop Down List for Data Validation (8 Ways)
- Excel Data Validation Drop Down List with Filter (2 Examples)
- How to Turn on Autocomplete in Excel (3 Easy Ways)