Restricting a dropdown list by a substring

[zzzzzzzzz]

When clicking on Cell1, I would like to see a dropdown meny of the content in Range1. If typing a substring, say "alg", the dropdown should only display the subset of Range1 that starts with "alg". The point, of course, being that after a sufficiently long substring, only one choice remains. Then pressing Enter should set the value in Cell1 to that choice. In the situation when there are only a handful of remaining choices due to a long substring, it would be great if the choice also could be made by using the up and down keys plus Enter.

 

[excel_learnerz]

Excel provides a built-in feature called "Data Validation" that can create a dropdown list in a cell using values from a range. However, this feature does not support filtering the list based on a typed substring. To achieve the desired behavior, you can use a combination of Data Validation and VBA.

Please follow these steps:

1. Set up Data Validation: a. Select the cell where you want the dropdown list (Cell1). b. Go to the "Data" tab and click "Data Validation." c. In the "Allow" field, choose "List." d. In the "Source" field, enter the range of cells containing the list of items (Range1). e. Click "OK."
2. Add VBA code for filtering the list based on the typed substring: a. Press Alt + F11 to open the VBA editor. b. Double-click on the sheet name where Cell1 is located in the "Project Explorer" pane on the left. c. Paste the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTyped As String
Dim rngList As Range, cell As Range
Dim rngFiltered As Range

' Set the range where the original list of items is located (Range1)
Set rngList = Range("A1:A10") ' Adjust this range to match Range1

' Set the cell where the dropdown list is located (Cell1)
If Target.Address = "$B$1" Then ' Adjust this address to match Cell1
strTyped = Target.Value

' Filter the original list based on the typed substring
For Each cell In rngList
If LCase(Left(cell.Value, Len(strTyped))) = LCase(strTyped) Then
If rngFiltered Is Nothing Then
Set rngFiltered = cell
Else
Set rngFiltered = Union(rngFiltered, cell)
End If
End If
Next cell

' Update the dropdown list with the filtered list
If Not rngFiltered Is Nothing Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & rngFiltered.Address
End With
End If
End If
End Sub

d. Adjust the range address (A1:A10) to match Range1 and the cell address ($B$1) to match Cell1 in the code. e. Close the VBA editor.

Now, when you type a substring in Cell1, the dropdown list will be filtered based on the typed substring, and you can select the remaining items using the up and down arrow keys.

Note that this solution might not be perfect in handling every scenario, and you may need to adjust the VBA code according to your specific requirements. Also, make sure to save the workbook as a macro-enabled file (.xlsm).

 

[zzzzzzzzz]

Unfortunately, when I write the search string, I have to press Enter for the VBA code to be called and thereby restricting the list. It would have been great if the dropdown list automatically got restricted when typing the search string without having to press Enter. But my feeling is that this can't be done.

Thank you so much for the very pedagogical answer. It seems to me that it is the best thing that can be done and it will be good enough for me. I really appreciate your help!!

 

[Fluff]

Have a look here Macro to create searchable data validation+combobox

 

[RoryA]

FYI, this feature is actually coming to desktop 365 but may take a little while to roll out.