Performance Issue In Dynamic Dropdown Search List In Excel

I have created dropdown list which gives suggestions from existing data (like google search). I have followed the steps mentioned in the link to get the expected result. I have cha

Solution 1:

I don't know about performance, but give the following a try. Your data is supposed to be in sheet "db", from row 2 onwards. Insted of a combobox, I place a textbox (TextBox1) and a list (ListBox1) in a UserForm.

PrivateSub TextBox1_Change()
    Dim v AsStringDim YourInput AsStringDim iIdx AsLongDim CharNumber AsInteger

    YourInput = TextBox1.Text

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ListBox1.Clear

    lLastRow = Worksheets("db").Cells(3, 1).End(xlDown).Row
    CharNumber = Len(YourInput)

    For iIdx = 2To lLastRow
        v = Worksheets("db").Cells(iIdx, 1).TextIf LCase(Left(v, CharNumber)) = LCase(YourInput) Then
            ListBox1.AddItem v
        EndIfNext

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = TrueEndSubPrivateSub ListBox1_Click()
    MsgBox ("Your selection: " & ListBox1.Text)
    'do your stuffEndSub

Solution 2:

I can't solve what you pretend. I was able to fill a list according to input rather fast. However, code finds your input anywhere in the string, not in the beginning. Depending on your data, the following code might help you, or may be you can reformulate it to fit your needs. Again, data is supposed to be in sheet "db", from row 2 onwards, and there is a textbox (TextBox1) and a list (ListBox1) in a UserForm.

PrivateSub UserForm_initialize()
    Dim lLastRow AsLong'Get last row of data
    lLastRow = Worksheets("db").Cells(3, 1).End(xlDown).Row
    ReDim DirArray(1To1)
    j = 1'Add all the data to an Array (DirArray)For i = 3To lLastRow
        ReDimPreserve DirArray(1To j)
        DirArray(UBound(DirArray)) = Worksheets("db").Cells(i, 1).Value
        j = j + 1NextEndSubPrivateSub TextBox1_Change()
    Dim YourInput AsString

    YourInput = TextBox1.Text

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ListBox1.Clear
    LArray = Array()

    LArray = Filter(DirArray, YourInput, True, vbTextCompare)

    ListBox1.List = LArray

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = TrueEndSubPrivateSub ListBox1_Click()
    MsgBox ("Your selection: " & ListBox1.Text)
    'do your stuffEndSub

Solution 3:

I did something similar to a google search in my excel app. My code waits 2 seconds after the last keystroke, then runs through another sheet to get close matches. This way it wasn't running through the DB whilst typing..

Wait until user has stopped typing in ComboBox to run macro (VBA)

Someone had suggested I actually look at this post but it was not helpful to my particular challenge.