Filtering a Listbox with a textbox in a userform

[ArisZ]

Hey all. I have been trying for days, read other posts, and can't get this to work.

I have a user form. On this form there is a listbox named Values. This box has it's data RowSource set to 'Costsrt'!B5:B1171.
I have a TextBox above it labeled Search_Box.

What I want, is being that I have 1166 entries, to be able to type into the search box, and as I type, it will only keep the lines that include
what I am typing. So if I type safety for example, then the only lines it should show are lines that contain the word safety in it.

Can anyone help me with this? I tried and can't get it to work.

Also, I see a lot of posts saying to use the command line Me.RefreshList

If I put this in, I always get an error. Is this an old line? After Me. I don't even have that as a valid choice.

Thanks in advance!!

 

[ArisZ]

Anyone have any suggestions?


Cross post here: http://www.excelforum.com/excel-pro...with-a-textbox-in-a-userform.html#post3851953

 

[RoryA]

Clear the RowSource property of the list and use something like this:

Private Sub Search_Box_Change()
    Dim vList
    vList = Application.Transpose(Me.Values.List)
    Me.Values.List = Filter(vList, Search_Box.Text)
End Sub


Private Sub UserForm_Initialize()
    Me.Values.List = Sheets("Costsrt").Range("B5:B1171").Value
End Sub

 

[ArisZ]

Thanks! I'll let you know how it works!

 

[Kyle123]

That's spookily close to mine on EF, though I reckon mine is a bit more efficient ;p

Dim data


Private Sub TextBox1_Change()
    Me.ListBox1.List = Filter(data, Me.TextBox1.Text)
End Sub


Private Sub UserForm_initialize()
    data = Application.Transpose(Sheets("Costsrt").Range("B5:B1171").Value)
    Me.ListBox1.List = data
End Sub

 

[RoryA]

That's spookily close to mine on EF

Are we great minds or fools then?

 

[Kyle123]

Definitely the latter!

 

[greengbo]

This works well as long as the list box is only one column. I would like to display multiple columns in my listbox, but when I change the rage for the listbox to include more columns, it causes a type-mismatch when I try to run the search. How do I display multiple columns without causing an error?

Also, I noticed if I make a typo in the textbox and back space, the listbox filter does not refresh back to before the typo. is it possible to have the list refresh with any changes?

Thank you in advance for any help!