Listbox Search String in Cells for Multiple Keywords

JLmlb15

New Member
Joined
Dec 18, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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:


VBA 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:
 

JLmlb15

New Member
Joined
Dec 18, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!

 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

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?
 

JLmlb15

New Member
Joined
Dec 18, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top