Filter List Box values based on multiple text boxes

Hello Dear Members,

I got a situation make me confuse.

So, I have one sheet named "MainDataBase" which include range of data with multiple columns and rows.

If you check attached excel file you will see when I try to edit my data , firstly I bring full range of data into list box.

Afterwards I'd like to filter the same data placed in list box based on multiple text boxes.

For the first text box you can see my code (I found that code from internet). But actually I don't know how can I modify it for my situation.

I am saying what exactly I need : for instance if I filter for worker name I want to see all list box column, because it may need to filter further for other text boxes.

Thanks for all your support. I am so glad being part of this kind of community.

another option

Dim MTable As Variant

Private Sub UserForm_Initialize()
Dim LRow As Long
Dim LCol As Long


With Worksheets("MainDataBase")
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
    MTable = .Range(.Cells(5, 1), .Cells(LRow, LCol))
End With

With Me.Controls("DBListBox")
    .Clear
    .ColumnHeads = False
    .ColumnCount = UBound(MTable, 2)
    .List = MTable
    .ColumnWidths = "180;155;155;90;90;102;85;80;75;1"
    .TopIndex = 0
    
    
End With
End Sub


Private Sub WorkerNameTxb_Change()

Dim i As Long, j As Long
Dim MDB As Worksheet:    Set MDB = Worksheets("MainDataBase")

Me.DBListBox.Clear
If MDB.Range("A" & MDB.Rows.Count).End(xlUp).Row > 1 And Trim(Me.WorkerNameTxb.Value) <> vbNullString Then
    For i = LBound(MTable) To UBound(MTable)
        If InStr(1, MTable(i, 1), Trim(Me.WorkerNameTxb.Value), vbTextCompare) Then
            With Me.DBListBox
                .AddItem MTable(i, 1)
                For j = 1 To 9
                  .Column(j, .ListCount - 1) = MTable(i, j + 1)
               Next j
            End With
        End If
    Next i
End If
If Me.DBListBox.ListCount = 1 Then Me.DBListBox.Selected(0) = True

End Sub

Vlaue in red must go at the very top of the module, before any code

Dear, Sintek your code did't work with me ((.

Dear, ImranBhatti your code is working well for each text box separately. but when I try to filter two or more text boxes at the same time it doesn't work properly.

Try change to

Option Explicit

Private MTable

Private Sub UserForm_Initialize()
    Dim i As Long
    With Worksheets("MainDataBase").Range("a4").CurrentRegion
        MTable = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    ReDim Preserve MTable(1 To UBound(MTable, 1), 1 To UBound(MTable, 2) + 1)
    For i = 1 To UBound(MTable, 1)
        MTable(i, UBound(MTable, 2)) = i + 4
    Next
    With Me.Controls("DBListBox")
        .ColumnCount = UBound(MTable, 2) - 1
        .List = MTable
        .ColumnWidths = "180;155;155;90;90;102;85;80;75;1"
        .TopIndex = 0
    End With
End Sub


Private Sub WorkerNameTxb_Change()

    Dim i As Long
    Dim arrList As Variant
    With Me.DBListBox
        If Me.WorkerNameTxb.Value <> "" Then
            For i = .ListCount - 1 To 0 Step -1
                If Not UCase$(.List(i, 0)) Like "*" & UCase$(Me.WorkerNameTxb.Value) & "*" Then .RemoveItem i
            Next
        Else
            .List = MTable
        End If
    End With
End Sub

Dear, Jindon. Thanks for your consideration. I used your code and it worked well when I filter text boxes more than one at the same time. But little problem I have. Suppose that I filtered list box by two or more text boxes and then I clear value of one of those text boxes, list box does not show me result according kept text box values. or another example is that, I wrote "Aqil" in the worker name text box and then if I remove last letter "L" list box will not show result relate to "Aqi". How can I solve this problem. Thanks a lot for your support.

Originally Posted by Medex

I wrote "Aqil" in the worker name text box and then if I remove last letter "L" list box will not show result relate to "Aqi". How can I solve this problem. Thanks a lot for your support.

If you want this to happen, it will be much slower.
Current setting is to Initialize the ListBox when any textbox is chaned to empty, so if you change any one text box to empty, it will filter the by the rest of text boxes if any of them are filled.

If you don't care about the speed, change

    With EditData
        If TB.Value = "" Then .DBListBox.List = MTable

to

    With EditData
        .DBListBox.List = MTable

In class module called MyTB

Changed textbox names and more.

Try the attached.

Dear, Jindon I am really grateful to you for your all support and appreciate your help. Your code is high level but I understood main principal. I learned much more things.

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Dear Jindon, I am really sorry that I ask about this thread again. but it happen problem with me and I can't solve it.

I just copy and paste your code into my original file and create proper class module. But when I run code I got error which show that problem is about user form activation.

Please help me with this problem.

All you have to do is change all TextBox names.
Text box for;
Worker Name -> TB1
Division -> TB2
Position -> TB3
.
.
Total -> TB10.

Thanks a lot!