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.

 

[Akuini]

Hi, @johnnyL
Here's an example of a search textbox + listbox in a userform.
1. The search ignores the keywords order, so keywords "ma la" will find "Maryland" and "Alabama"
2. Double-clicking an item in the listbox OR selecting an item then hit ENTER will get you to the intended row in the sheet.

Note:
I assumed:
1. col A has unique data.
2. the search is only for col A.

'Option Explicit
Dim va As Variant
Dim oldValue As String
Dim q As Long
Private Sub UserForm_Initialize()
    
    q = 3 'number of column
    ListBox1.ColumnCount = q
    ListBox1.ColumnWidths = "150,70,70"
    
    With Sheets("Sheet1")
        va = .Range("A3", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, q)
    End With
       
    'ListBox1.List = va
End Sub

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
Dim flag As Boolean

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
            
            For i = 1 To UBound(va, 1)
                
                flag = True
                For Each z In Split(tx, " ")
                    If InStr(1, va(i, 1), z, vbTextCompare) = 0 Then flag = False: Exit For 'case insensitive search
                Next
      
                If flag Then
                    For h = 1 To q
                        .AddItem
                        .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
        Application.StatusBar = i & " : " & j & " : " & Timer - t
        

End Sub


Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call getTo
End Sub

Sub getTo()
Dim y As Long
    y = ListBox1.ListIndex
       If y > -1 Then
'            MsgBox ListBox1.List(y, 0)
            res = Application.Match(ListBox1.List(y, 0), Sheets("Sheet1").Range("A:A"), 0)
                If IsNumeric(res) Then
                    Application.Goto Reference:=Worksheets("Sheet1").Range("A" & res), Scroll:=True
                    Unload Me
                End If
       End If
End Sub

Private Sub Listbox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then Call getTo
End Sub

The sample workbook:

johnnyL - is-it-possible-to-have-a-search-listbox-that-gives-a-dropdown-of-matches-dynamically

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[johnnyL]

Thank you @Akuini for responding to my question.

1) The column A data will have no duplicates, aka all unique entries
2) The search is only for the Column A

With that said, I want the 'keyword' to pick up the 'exact' match, anywhere in a string from the Column A cell being checked.
In other words if 'john' is typed in, It might dynamically display, in a drop down, 'John Smith' and 'Bob Johnson' and 'Billy john King'.

I haven't tested your code from your post here, but from your comments it sounds like it would be a little off from my intentions.

Clicking on one of the results and directing to that row sounds like it would be spot on.

 

[Akuini]

With that said, I want the 'keyword' to pick up the 'exact' match, anywhere in a string from the Column A cell being checked.
In other words if 'john' is typed in, It might dynamically display, in a drop down, 'John Smith' and 'Bob Johnson' and 'Billy john King'.

By exact match do you mean match the whole word?
So if you type 'jo' then it will show 'Jo Ann' but not 'John Smith?

 

[johnnyL]

@Akuini I think my last post answered that.

In other words if 'john' is typed in, It might dynamically display, in a drop down, 'John Smith' and 'Bob Johnson' and 'Billy john King'.

Basically previous and after characters don't matter.

It is more of a non case sensitive search anywhere in the string. ie. Jon = jon = JON = jON, etc

My apologies if I was not clear in my last post.


So to answer your question, 'jo' should show both 'Jo Ann' and 'John Smith'.

 

[Akuini]

@Akuini
It is more of a non case sensitive search anywhere in the string. ie. Jon = jon = JON = jON, etc

So to answer your question, 'jo' should show both 'Jo Ann' and 'John Smith'.

1. The code I provide in the link is case insensitive searching.
2. The search ignores the keywords order, so keywords "ma la" will find "Maryland" and "Alabama". You don't want this?

 

[Akuini]

Please try the sample workbook I provided, and let me know what adjustment you need.

 

[johnnyL]

That is what I was saying, ignoring the keyword order is not desired. Everything else sounds good.

If 'ma la' is entered, 'la ma' should not be returned as a result.

 

[johnnyL]

2. The search ignores the keywords order, so keywords "ma la" will find "Maryland" and "Alabama". You don't want this?

No! 'ma la' is 5 characters in that exact order, just not case sensitive.

 

[Akuini]

I modified Private Sub TextBox1_Change, to not ignore the keywords order

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
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
            
            For i = 1 To UBound(va, 1)
                flag = True
                k = 0
                For Each z In Split(tx, " ")
                        w = InStr(1, va(i, 1), z, vbTextCompare) 'not case sensitive
                        If w < k Or w = 0 Then
                              flag = False
                              Exit For
                        End If
                        k = w
                Next
      
                If flag Then
                    For h = 1 To q
                        .AddItem
                        .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
        Application.StatusBar = i & " : " & j & " : " & Timer - t
        
End Sub