ExtendOffice.com offers a VBA solution that worked for me in Excel 2016. Here’s my description of the steps. I included additional details to make it easier. I also modified the VBA code slightly. If this doesn’t work for you, retry the steps or check out the instructions on the ExtendOffice page.
- Add data validation to a cell (or range of cells). Allow = List. Source = [the range with the values you want for the auto-complete / drop-down]. Click OK. You should now have a drop-down but with a weak auto-complete feature.
- With a cell containing your newly added data validation, insert an ActiveX combo box (NOT a form control combo box). This is done from the Developer ribbon. If you don’t have the Developer ribbon you will need to add it from the Excel options menu.
- From the Developer tab in the Controls section, click “Design Mode”. Select the combo box you just inserted. Then in the same ribbon section click “Properties”. In the Properties window, change the name of the combo box to “TempCombo”.
Press ALT + F11 to go to the Visual Basic Editor. On the left-hand side, double click the worksheet with your data validation to open the code for that sheet. Copy and paste the following code onto the sheet. NOTE: I modified the code slightly so that it works even with
Option Explicitenabled at the top of the sheet.
Option Explicit Private Sub Worksheet_SelectionChange(ByVal target As Range) 'Update by Extendoffice: 2018/9/21 ' Update by Chris Brackett 2018-11-30 Dim xWs As Worksheet Set xWs = Application.ActiveSheet On Error Resume Next Dim xCombox As OLEObject Set xCombox = xWs.OLEObjects("TempCombo") ' Added this to auto select all text when activating the combox box. xCombox.SetFocus With xCombox .ListFillRange = vbNullString .LinkedCell = vbNullString .Visible = False End With Dim xStr As String Dim xArr If target.Validation.Type = xlValidateList Then ' The target cell contains Data Validation. target.Validation.InCellDropdown = False ' Cancel the "SelectionChange" event. Dim Cancel As Boolean Cancel = True xStr = target.Validation.Formula1 xStr = Right(xStr, Len(xStr) - 1) If xStr = vbNullString Then Exit Sub With xCombox .Visible = True .Left = target.Left .Top = target.Top .Width = target.Width + 5 .Height = target.Height + 5 .ListFillRange = xStr If .ListFillRange = vbNullString Then xArr = Split(xStr, ",") Me.TempCombo.List = xArr End If .LinkedCell = target.Address End With xCombox.Activate Me.TempCombo.DropDown End If End Sub Private Sub TempCombo_KeyDown( _ ByVal KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Select Case KeyCode Case 9 ' Tab key Application.ActiveCell.Offset(0, 1).Activate Case 13 ' Pause key Application.ActiveCell.Offset(1, 0).Activate End Select End Sub
Make sure the the “Microsoft Forms 2.0 Object Library” is referenced. In the Visual Basic Editor, go to Tools > References, check the box next to that library (if not already checked) and click OK. To verify that it worked, go to Debug > Compile VBA Project.
Finally, save your project and click in a cell with the data validation you added. You should see a combo box with a drop-down list of suggestions that updates with each letter you type.