Filter a listbox containing multiple fields/columns using a single searchbox

[jayfromthebay]

https://www.youtube.com/watch?v=3cJinHSSkuI


Here is a link explaining exactly what I am trying to accomplish using excel. I am trying to use only ONE textbox that will dynamically search (search any character as I type) through multiple columns and have the listbox filter base off of text in the textbox.

Please, Ive searched for days and I am unable to find any help regarding how to do this.

 

[Anthony47]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

https://www.youtube.com/watch?v=3cJinHSSkuI

Here is a link explaining exactly what I am trying to accomplish using excel. I am trying to use only ONE textbox that will dynamically search (search any character as I type) through multiple columns and have the listbox filter base off of text in the textbox.

Please, Ive searched for days and I am unable to find any help regarding how to do this.

If you are talking about Excel and you wish to dynamically filter the intended RowSource against the content of a textbox and populate a listbox, then you may have a look to this workbook:
https://www.dropbox.com/s/hfhp8a3hcxct8on/LBox_Demo.xlsm?dl=0

Press the ShowForm button and a Form with a ListBox and a Textbox will appear.
Columns B:F of the first sheet are used to populate the listbox; when you enter text in the textbox only the rows that contain that string in one of the Cells (any of the 5 columns) will populate the ListBox.

I don't use the RowSource property of the listbox, but the List property; I use an array (sArr), whose containt is reviewed every TextBox1_Change.

Hope the example can help.
Bye

 

[jayfromthebay]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Thanks Anthony! I can definitely work with this. Would you happen to know how to get the list box to sort data (Ascending or Descending)?? Sorry Im much of a novice here.

 

[jayfromthebay]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Basically I am trying to add a sort button over each column that will sort each of the colums data.

 

[Anthony47]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

I modified the demo file that I already shared.

Now the form has 6 OptionButtons that define which column has to be used for sorting; their code is quite the same.

There is also a "bubble sort" routine (Function bbSort) that actually sort the array used to populate the listbox.

HTH
Bye

 

[ANMHN]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Hi, Anthony
Your method is truly amazing. But is there a way to populate textboxes by selecting a row on the listbox.
Thank you

 

[Anthony47]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Hi, Anthony
... Is there a way to populate textboxes by selecting a row on the listbox.
Thank you

Well, listboxes are designed to allow the user to select an item of the available list and then use the selection for some purpose...
The properties List (all the L.B. content) and ListIndex (the pointer to the selection) can be used. For example, this will move the first column of the selected item to TextBox2 and the third column to TextBox3:

Private Sub ListBox1_Click()
With Me.ListBox1
    Me.TextBox2.Value = .List(.ListIndex, 0)        '<<< Column 1
    Me.TextBox3.Value = .List(.ListIndex, 2)        '<<< Column 3
End With
End Sub

The demo file is available here.
https://www.dropbox.com/s/lvihcsrl6nxv75a/lbox-n-tbox_demo.xlsm?dl=0

bye

 

[ANMHN]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Thank you so much for helping. What i am trying to accomplish here is to search the listbox , select a row and edit them using the textboxes mentioned. Afterwards can i send them back to both the worksheet and listbox simultaneously(both will be updated).
Thanks again for helping me.

 

[ANMHN]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Thank you so much for helping. What i am trying to accomplish here is to search the listbox , select a row and edit them using the textboxes mentioned. Afterwards can i send them back to both the worksheet and listbox simultaneously(both will be updated).
Thanks again for helping me.

 

[Anthony47]

Re: How to filter a listbox containing multiple fields/columns using a single searchbox

Using vba most of the ideas can be moved to reality...

In your case I think you should first evaluate using the Excel buil in data form, that allow you searching with various criterias in a data table, or just scan the records one after one, and modify the needed fields, or add records; or delete some of them.
Of course a customized userfor can be developed, if it offers the user a simpler working procedure. In case you prefer developing the customized userform I (and many others on the forum) can provide guidance and suggestions.

Bye