Auto complete when typing in drop down in Excel where list is filled using VBA

Question

I am using the below code to insert data into a drop down list from another sheet. This is achieved when the user selects a certain choice from another drop down list.

lstRow = Sheets("Data Sheet").Range("D" & Rows.Count).End(xlUp).Row
Sheets("Data Insert").Range("C3").Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="='Associated British Ports'!$G$7:$G" & lstRow
    .IgnoreBlank = False
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Invalid Selection"
    .InputMessage = ""
    .ErrorMessage = _
    "Please select a user from the list or select New User as the configuration type."
    .ShowInput = True
    .ShowError = True
End With

I want to add in the function that when the user types in a few letters it searches through the list and eliminates anything that doesn't contain that. I.E. say I have the below in the dropdown: A Thomas c Smith f Graham t Evans s davids B matthews

And the user types in "th" The remaining values should be

A Thomas c Smith B matthews

Even a simplified version where the user must type in the name in the form of A Th.... To return A Thomas would be okay if the above is not doable.

I have seen this http://www.ozgrid.com/Excel/autocomplete-validation.htm and this Excel data validation with suggestions/autocomplete

but i dont think I'm not sure how to integrate this with the above code, or it is even possible!

Could anyone help me out please?

TIA :)

What is it you want? You seem to have lost that part of your question - "I want to I have seen..." — Doug Glancy, May 20 '16 at 13:54

score 2 · Accepted Answer · answered May 20 '16 at 14:21

Here is my SAYT (Search As You Type) function. My form has a listbox control with a list of users and a textbox control that you can use to search the list.

Private Sub txtSearch_Change()
    Dim x As Integer

    lstUsers.ListIndex = -1
    For x = 0 To lstUsers.ListCount - 1
        lstUsers.ListIndex = x
        If InStr(1, LCase(lstUsers.Text), LCase(txtSearch.Text), vbTextCompare) > 0 _
        Or InStr(1, LCase(lstUsers.List(x, 1)), LCase(txtSearch.Text), vbTextCompare) > 0 _
        Then
            Exit Sub
        End If
    Next x
End Sub

Private Sub txtSearch_KeyPress(ByVal KeyAscii As msforms.ReturnInteger)
    If KeyAscii = 13 Then
        txtSearch.Text = lstUsers.Text
    End If
End Sub

As you type, the txtSearch_Change event fires with every keystroke, and it loops through the listbox values until it finds the first match and selects it. We also check the KeyPress event to see if the user pressed Enter (ASCII 13) to autocomplete the search. Mine is case insensitive (I LCase everything), but you could easily modify it to be case sensitive (or even add a checkbox so the user can select case sensitivity!)

Thank you, I will try this tomorrow and let you know :) – TechyChick94 May 22 '16 at 06:02 — TechyChick94, May 22 '16 at 06:02
Nice approach to Search As You Type – Adarsh Madrecha May 23 '16 at 08:30 — Adarsh Madrecha, May 23 '16 at 08:30

Auto complete when typing in drop down in Excel where list is filled using VBA

1 Answers1

Linked