Listbox Search String in Cells for Multiple Keywords

[JLmlb15]

Hi Everyone, I've created a listbox search function in VBA that will list products based on the assigned description in my database. My current code will work for only consecutive words, but I would like to be able to generate results regardless of order.

For example, the current code will return me "Red Pair of Tube Socks" if I search for "Red Pair" or "of Tube S". I'd like to be able to arrive at that result if I search for "Red Socks" or "Tube Red".

Does anyone have any advice how to do this? This is my current code:

Private Sub TextBox1_Change()
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Dim sh As Worksheet
Set sh = Sheets("ProductDatabase")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
'FOR LISTBOX HEADER
Me.ListBox1.AddItem "Item Number"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Description"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Unit of Measure"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Vendor"


Me.ListBox1.Selected(0) = True

For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 2))
p = Me.TextBox1.TextLength

If LCase(Mid(sh.Cells(i, 2), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 4)






End With
End If
Next x
Next i

End Sub

 

[Akuini]

Could you upload a sample workbook to a free site such as dropbox.com or google drive & then put the link here?
It will make it easier to test and find a solution.

 

[Akuini]

In the mean time, I have an example of a searchable combobox that is ignoring the keywords order (that's is what you want, right?).
The code uses Filter function to do that type of search. I think I can amend the code to work on your listbox.
Here's part of the code:

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList, 0)) Then
        With Sheets("deList")
            ary = Application.Transpose(.Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value)
        End With
                
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
           .List = ary
           .DropDown
    
    ElseIf Not IsError(Application.Match(.Value, vList, 0)) Then
        Range(xCell) = .Value
    
    Else
        Range(xCell) = .Value
        .List = vList
    End If
End With
End Sub

The workbook:

deCombobox - sheet,1 cell, searchable,filter - example 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[JLmlb15]

Hi Akuini! Thanks so much for taking a look at it. I've posted my file into a dropbox link below. Any help or advice/recommendations would be super helpful. The two issues I'm having is 1.) I'd like the search option to return keyword results that aren't in sequential order (ie. if I search for "Tape Blue" I'd like for "Navy Blue Polyblend Tape" to show).

My second issue that I cannot find a workaround for is that if someone begins to populate the form and they want to delete an earlier entry, the vba code will no longer place the desired line item at the bottom of the list. I was thinking I could conditionally format text to appear in deleted lines to act as a placeholder, but I have not been able to figure that out.

Thanks again for looking at this!

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Akuini]

Ok, try this.
1. I added listbox2 to populate the header there.
2. You already have formula in colD:F to populate the corresponding data with Item number, so the code in the userform will only insert Item Number in col C.

The workbook:

Collins Bid List (1) - JLmlb15 - 1.xlsm

Shared with Dropbox

www.dropbox.com

My second issue that I cannot find a workaround for is that if someone begins to populate the form and they want to delete an earlier entry, the vba code will no longer place the desired line item at the bottom of the list. I was thinking I could conditionally format text to appear in deleted lines to act as a placeholder, but I have not been able to figure that out.

I don't understand what you mean, can you explain in more detail?

 

[Akuini]

Sorry, the code in the above file is wrong.
Use this one instead:

Collins Bid List (1) - JLmlb15 - 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[JLmlb15]

This is exactly what I was hoping for! Not only does it return the desired search result if the words are out of order, but it also returns results based on partial words. Thanks for your help on this!

I believe you fixed my second issue as well... I wrote the code to populate the 'Collins Bid Form' worksheet so that if there were 4 items already listed (rows 22 through 25) the next item would be added to row 26. My issue was if a user decides to remove an item (say they delete the contents of C23), the next time they attempt to populate an item it won't appear in row 26 but rather row 23. However this no longer appears to be the case. I really appreciate the assistance.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.