Is it possible to have a search listbox that gives a dropdown of matches dynamically

[johnnyL]

This would be in regards to a previous thread located here.

Basically in that thread, I had assistance with using a textbox to search a column of data and while typing in the textbox, matches to the currently typed text are highlighted in the column that is being searched. It works great!!!

Bothersome though, is the fact that the column of data is nearly 1000 entries, so it takes some time to scroll through the list to find the colored matches.

I was wondering if there was a way to make the textbox have a drop down of dynamic matches as the textbox is being typed into so that the user could type in a few letters and then click an item in the drop down list and be directed to the row for that match that was selected from the drop down list.

Any assistance is appreciated, as always.

Like I said, how it is setup now works, but just a bit of a pain when doing several searches and having to do all of that scrolling for each search.

 

[johnnyL]

Many thanks @Akuini! I have successfully adapted the code that you provided to work for my situation. Thanks to the code that you provided, all of that scrolling for every search result is in the past.

 

[johnnyL]

Bonus Question:

Is there a way to add a dynamic 'total matches found thus far' box to the right of where the text is being entered on the user form?

 

[johnnyL]

According to the status bar, the 'j' variable holds up to a maximum # of 200 results returned. I am not sure how to put that variable in the user form though. User form approach is new to me.

 

[Akuini]

I'm sorry, actually the in post #10 is a bit flawed.
You can try that "ee ee" will match "Beavercreek, Ohio", it shouldn't.
So use this one instead:

Private Sub TextBox1_Change()

Dim results As Variant
Dim x, z, t
Dim i As Long, j As Long, h As Long
Dim tx As String, zx As String
Dim flag As Boolean
Dim k As Long, w As Long

t = Timer
tx = Trim(UCase(TextBox1.Text))
'n = 200 'limit number of items shown in listbox

If tx <> oldValue Then
    With ListBox1
        .Clear
        If tx <> "" Then
            .Clear
            For i = 1 To UBound(va, 1)
                flag = True
                k = 1
                zx = va(i, 1)
                For Each z In Split(tx, " ")
                        w = InStr(k, zx, z, vbTextCompare) 'not case sensitive
                        If w = 0 Then
                            flag = False
                            Exit For
                        Else
                            k = w + Len(z)
                        End If
                Next

                If flag Then
                    .AddItem
                    For h = 1 To q
                        .List(j, h - 1) = va(i, h)
                    Next
                
                    j = j + 1
'                    If j = n Then Exit For
                End If

            Next
        End If

    End With
End If
        oldValue = tx
        Label1.Caption = "Items found: " & ListBox1.ListCount

End Sub

Is there a way to add a dynamic 'total matches found thus far' box to the right of where the text is being entered on the user form?

Yes, you can put that info to a label. In the code I added this line:
Label1.Caption = "Items found: " & ListBox1.ListCount
of course you need to put a Label in the Userform

But do you need to limit how many items are shown in the listbox, as this line does:
n = 200 'limit number of items shown in listbox
I use that in case you have a large data, say more than 5K rows, without limitation it could slow down the process.
But you said:

Bothersome though, is the fact that the column of data is nearly 1000 entries,

So I don't think your need that, so you can remove these 2 lines (actually I've already commented them):
n = 200 'limit number of items shown in listbox
and
If j = n Then Exit For

 

[johnnyL]

The 'Label1.Caption' Line throws an error 424, Object required.

Edit: Disregard, I see what you are saying now. I have to add the label to the user form first, then run code.