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.

 

[ANMHN]

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

Hello Anthony,
Thank you for your suggestion. But i am trying to create a customized userform with above mentioned abilities. The Code i used to write back data to excel cells from textbox (Populated from selected row in listbox) is a bit slower. I cant find another way to find Activecell in excel sheet (Selected row in listbox). The Code i am using is,

Private Sub cmdamend_Click()

    Application.ScreenUpdating = False
    With Sheets("Transactions")
        Cells.Find(what:=ListBox1.List(ListBox1.ListIndex), after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True, searchformat:=False).Activate
        Cells.Findnext(after:=ActiveCell).Activate
        ActiveCell.Offset(0, 9).Value = UserForm1.cbpay1.Text
        ActiveCell.Offset(0, 10).Value = UserForm1.tbtimestamp2.Text
        End With
        ListBox1.ListIndex = ListBox1.ListIndex
    Application.ScreenUpdating = True


End Sub

I do have another problem with the comboboxes which will be populated by the listbox selection. the dropdown in the comboboxes should be populate with datavalidation corresponding cell in excel sheet (same as the row selected in listbox). The code for that is,

Private Sub cbpay1_DropButt*******()With Sheets("Transactions")
        Cells.Find(what:=ListBox1.List(ListBox1.ListIndex), after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True, searchformat:=False).Activate
        Cells.Findnext(after:=ActiveCell).Activate
Me.cbpay1.RowSource = ActiveCell.Offset(0, 9).Validation.Formula1


End With
End Sub

This code works sometimes and crashes showing "Object defined error".
Hope you can help me with this.
Thank you

 

[Anthony47]

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

I am sorry I missed the notification for your answer.

A userform is complex entity, results are related to the ability in managing in realtime its components, exploiting their properties and using their events.

If you filter the "RowSource" that populates the listbox then its a little bit tricky to identify wich row has been selected. The safest way is to scan the RowSource area against all the datacolumns of the listbox, but knowing your data structure could suggest alternate (simpler) method. For example, is any column "unique" in your data, so that you can "match" that column only?

Possible ways to speedup your code are:
1) limit the search to the rowsource area
2) avoid cell selection

For example, working with the demo workbook I published:
-when selecting an item in the listbox I populate TextBox2 and TextBox3 with the data from column 1 and column 3
-these TextBoxes can be modified
-I added a button UPDATE; when the button is pressed I use the following code to update the sourcedata:

Private Sub CommandButton1_Click()
'Manage the UPDATE button:
Dim sRan As Range, tData As Range, cTBt
'
Set sRan = Range(Range("B2"), Range("B2").End(xlDown).End(xlToRight))
If Me.ListBox1.ListIndex >= 0 Then
    Set tData = sRan.Find(what:=Me.ListBox1.List(Me.ListBox1.ListIndex, 0), after:=sRan.Cells(1, 1), LookIn:=xlValues, _
       lookat:=xlWhole)
    If Not tData Is Nothing Then
        tData.Offset(0, 0) = Me.TextBox2      'Update Column 1 of the rowsource
        tData.Offset(0, 2) = Me.TextBox3      'Update column 3 of the rowsource
    End If
    'Force repopulating the ListBox:
    cTBt = Me.TextBox1.Text
    Me.TextBox1.Text = cTBt & " "
    Me.TextBox1.Text = cTBt
End If
End Sub

sRan is a reference to the original rowsorce for the Listbox; column B (column1 of the listbox) contains unique values, so I can search only that data and ignore the remaining columns of the listbox.
After the search column1 and column3 of the searched line are modified using the containt of the two textboxes; finally the Listbox is forced to repopulate
The demo file now contains these modification.

As far as the second problem you talk about, I did not understand it. But you should keep in mind that listboxes and comboboxes get populated the same way.
Thus you could reuse the same method I used to populate the listbox in the Sub UserForm_Initialize

Bye

 

[ANMHN]

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

I am sorry I missed the notification for your answer.

A userform is complex entity, results are related to the ability in managing in realtime its components, exploiting their properties and using their events.

If you filter the "RowSource" that populates the listbox then its a little bit tricky to identify wich row has been selected. The safest way is to scan the RowSource area against all the datacolumns of the listbox, but knowing your data structure could suggest alternate (simpler) method. For example, is any column "unique" in your data, so that you can "match" that column only?

Possible ways to speedup your code are:
1) limit the search to the rowsource area
2) avoid cell selection

For example, working with the demo workbook I published:
-when selecting an item in the listbox I populate TextBox2 and TextBox3 with the data from column 1 and column 3
-these TextBoxes can be modified
-I added a button UPDATE; when the button is pressed I use the following code to update the sourcedata:

Private Sub CommandButton1_Click()
'Manage the UPDATE button:
Dim sRan As Range, tData As Range, cTBt
'
Set sRan = Range(Range("B2"), Range("B2").End(xlDown).End(xlToRight))
If Me.ListBox1.ListIndex >= 0 Then
    Set tData = sRan.Find(what:=Me.ListBox1.List(Me.ListBox1.ListIndex, 0), after:=sRan.Cells(1, 1), LookIn:=xlValues, _
       lookat:=xlWhole)
    If Not tData Is Nothing Then
        tData.Offset(0, 0) = Me.TextBox2      'Update Column 1 of the rowsource
        tData.Offset(0, 2) = Me.TextBox3      'Update column 3 of the rowsource
    End If
    'Force repopulating the ListBox:
    cTBt = Me.TextBox1.Text
    Me.TextBox1.Text = cTBt & " "
    Me.TextBox1.Text = cTBt
End If
End Sub

sRan is a reference to the original rowsorce for the Listbox; column B (column1 of the listbox) contains unique values, so I can search only that data and ignore the remaining columns of the listbox.
After the search column1 and column3 of the searched line are modified using the containt of the two textboxes; finally the Listbox is forced to repopulate
The demo file now contains these modification.

As far as the second problem you talk about, I did not understand it. But you should keep in mind that listboxes and comboboxes get populated the same way.
Thus you could reuse the same method I used to populate the listbox in the Sub UserForm_Initialize

Bye

Thank You so much Anthony,
That code works great. I have edited the code to suit my requirements and it works well. As i mentioned earlier I am using several combo boxes with this userform and your code works well with them too. Now that i had some trouble with the first code (textbox1_change), it shows error 380 some times (rarely) when i run the combobox dropdown_click in userform. I am not sure if both are connected anyway please take a look at the codes below.

1. Code to populate the combobox dropdown (is this the right way to do this?)

Private Sub cbpay1_DropButt*******()Dim sRan As Range, tData As Range, cTBt
Set sRan = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
If Me.ListBox1.ListIndex >= 0 Then
Set tData = sRan.Find(what:=Me.ListBox1.List(Me.ListBox1.ListIndex, 0), after:=sRan.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
Me.cbpay1.RowSource = tData.Offset(0, 9).Validation.Formula1
End If
End Sub

2. Error 380 textbox1_change

Me.ListBox1.Column(i - 1, 0) = sArr(i, 1)

Thanks again

 

[Anthony47]

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

The code in error belong to this part:

'If one line only it is a bit more complex:
    Me.ListBox1.Clear
    Me.ListBox1.AddItem
    For i = 1 To UBound(sArr)
        Me.ListBox1.Column(i - 1, 0) = sArr(i, 1)
    Next i

The message probably say that sArr values cannot be assigned to ListBox1 columns; you should examine sArr contents to identify any possible reason.

In the Private Sub cbpay1_DropButt*******() code I see some of the instructions I used to modify the listbox original data in my demo file. I cannot understand if the way you used them is the right way to populate your cbpay1 combobox.

If you can share a demo file maybe we shall be able to work more on the case.

Bye

 

[ANMHN]

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

Sorry for the late reply. I have uploaded the demo file in below link,
https://www.dropbox.com/s/oqexvsi8b8xvzdb/project%201.xlsm?dl=0
The error part is in Userform1 on Worksheet Transactions. Please have a look.
Thank you.

 

[Anthony47]

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

Sorry for the late reply. I have uploaded the demo file in below link,
https://www.dropbox.com/s/oqexvsi8b8xvzdb/project%201.xlsm?dl=0
The error part is in Userform1 on Worksheet Transactions. Please have a look.
Thank you.

I did download the file, but don't know what to do to create the error; please clarify this.

Bye

 

[ANMHN]

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

Open userform1 by selecting any row in the page Transactions. Error occurs while clicking the combobox in userform1 more than one time. Please note that this error doesn't come frequently.
Thank you.

 

[Anthony47]

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

Open userform1 by selecting any row in the page Transactions. Error occurs while clicking the combobox in userform1 more than one time. Please note that this error doesn't come frequently.
Thank you.

The code that fails has nothing to do with what has been discussed in this thread...

You have several Comboboxes in the frame Receive Installments of UserForm1, and you are trying to assign their rowsource the same way you manage a data validation list:

Me.cbpay1.RowSource = tData.Offset(0, 9).Validation.Formula1   'this line fails...

Of course this fails because ComboBoxes and DropDown list are different objects.

Since the data validation list is assigned using the function "Indirect" you could evaluate this formula and get the address of the source data.
For example, working on cbpay1:

'Me.cbpay1.RowSource = tData.Offset(0, 9).Validation.Formula1       'REMOVED
'NEXT TWO LINES HAVE BEEN ADDED:
myName = Evaluate(Mid(tData.Offset(0, 9).Validation.Formula1, InStr(1, tData.Offset(0, 9).Validation.Formula1, "(", vbTextCompare)))
Me.cbpay1.RowSource = Names(myName).RefersTo

This does something that has a meaning to me

Check the behaviour, and if that is what you wish to do then rework the same way the other comboboxes' macros.

Bye

 

[ANMHN]

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

Thank you so much. That exactly was the problem. Sorry that I have been messed up that in the question and became a headache for you. I have another doubt about adding customer data without duplicates. Is it possible for you to look at the userform 2 ADD button, i have a macro that works. Is it possible that the new entry in textboxes loops through all columns in search for duplicate and approves. Once again thank you for your support.

 

[ANMHN]

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

Hello Anthony
I figured out the above mentioned problem. But can you help me with another issue to populate 4 textboxes by the listbox selection and based on a combobox value(all in userform).
If listbox selection column 9 is "not paid" then combobox value should be "first" and listbox selection columns 8,9,10,11 should be loaded to the textboxes. And if column 9 is "paid" and column 13 is "unpaid" then combobox value should be "second" and listbox selection columns 12,13,14,15 should be loaded to textboxes And so on.
Thanks you.