Filter ListBox with dependent comboboxes - Excel vba

Hi Carlo

Here's an approach you might try...

In the attached revised file, I've modified your FilterListBox1 code to the following:

'FILTER LISTBOX EQUALS
Sub FilterListboxEquals(Contr As String, LBCol As Long)
' Skip this when UF is started
If Init = False Then Exit Sub

    ' step backwards through list
    For i = ListBox1.ListCount - 1 To 0 Step -1
    'delete items not matching
        If ListBox1.List(i, LBCol - 1) <> Me.Controls(Contr).Text Then
            ListBox1.RemoveItem i
        End If
    Next i
End Sub

where 1) it does nothing when the chnage arise from UF initialization since I've declare a public varaible Init at the top of the UF code:

Public Init As Boolean

that becomes True after the UF appears:

'INITIALIZATION OF UF_Report_Global
Private Sub UserForm_Initialize()
    ' << your existing code not shown...>>

        lbl_Search.Visible = True 'Show the word "Search" in front of TextBox_Search.

    Application.ScreenUpdating = True
    Init = True

End Sub

and 2) it runs backwards through the list on screen removing any items not matching the value in the control which called it. For Team, Shift, Category and Status the code is similar but it passes different values (in bold below) to the filter code above:

'FILTER TEAM
Private Sub cbo_Team_Change()

    If Init = False Then Exit Sub
    ' call the = filter for visible column 2
    Call FilterListboxEquals("cbo_Team", 2)
   lbl_Filtered.Caption = ListBox1.ListCount  'Show number of filtered items
End Sub

'FILTER Shift
Private Sub cbo_Shift_Change()
   If Init = False Then Exit Sub
   ' call the = filter for visible column 6
   Call FilterListboxEquals("cbo_Shift", 6)
    lbl_Filtered.Caption = ListBox1.ListCount  'Show number of filtered items
End Sub

'FILTER CATEGORY
Private Sub cbo_Category_Change()
    ' call the = filter for visible column 12
   Call FilterListboxEquals("cbo_Category", 12)
    lbl_Filtered.Caption = ListBox1.ListCount  'Show number of filtered items
End Sub

'FILTER STATUS
Private Sub cbo_Status_Change()
    ' call the = filter for visible column 13
   Call FilterListboxEquals("cbo_Status", 13)
    lbl_Filtered.Caption = ListBox1.ListCount  'Show number of filtered items
End Sub

You can change one or all items but will need to use your Reset button to clear the filters and start again.

You will need to write a FilterListBoxGreater for the start date and similar for the end date but can call them in a similar way..

Revision 29 June 2023

Second file added which implements Start and End date filters (in a similar way) but disables controls as a filter is set- new purple refresh button added to UserForm (after Start Date to Shift filter entries).

Revision 02 July 2023

Replaced second file (correcting UF _Initialization line Init = False) and added following explanatory comments following user response...

Second file is similar to first but StartDate is filtered by this procedure:

'FILTER cboStartDate
Private Sub cboStartDate_Change()
    ' call the = filter for visible column 4
   Call FilterListboxMonthPlus("cboStartDate", 4)
    lbl_Filtered.Caption = ListBox1.ListCount  'Show number of filtered items

End Sub

'FILTER LISTBOX MONTH OR LATER
Sub FilterListboxMonthPlus(Contr As String, LBCol As Long)
' Skip this when UF is started
If Init = False Then Exit Sub

    ' step backwards through list
    For i = ListBox1.ListCount - 1 To 0 Step -1
    'delete items not matching
        If ListBox1.List(i, LBCol - 1) < CDate(Me.Controls(Contr).Text) Then
            ListBox1.RemoveItem i
        End If
    Next i
    ' prevent changes (except Reset)
    Me.Controls(Contr).Enabled = False
End Sub

The code for the end date changes is similar but calls new sub FilterListboxMonthLess which just reverses the operator giving this line:

If ListBox1.List(i, LBCol - 1) > CDate(Me.Controls(Contr).Text) Then

FilterListboxEquals (used by the chnage codes for other filters) now includes the same new lines above): 

    ' prevent changes (except Reset)
    Me.Controls(Contr).Enabled = False

Furthermore, since the reset code just calls the Sub UserForm_Initialize procedure, the sleected items (and all others) are re-enabled by the code in bold below):

'INITIALIZATION OF UF_Report_Global
Private Sub UserForm_Initialize()
    Sheets("REPORT").Activate

    Application.ScreenUpdating = False
    ' prevent triggering _Change procedures
    Init = False

        cbo_Team.Value = "All"
        cbo_Shift.Value = "All"
        cbo_Category.Value = "All"
        cbo_Status.Value = "All"

        cbo_Team.List = Array("All", "Team 1", "Team 2", "Team 3", "Team 4")
        cbo_Shift.List = Array("All", "First", "Second", "Third")
        cbo_Category.List = Array("All", "A", "B")
        cbo_Status.List = Array("All", "RE", "IR")
        ' (re)enable CB controls
        cboStartDate.Enabled = True
        cboEndDate.Enabled = True
        cbo_Team.Enabled = True
        cbo_Shift.Enabled = True
        cbo_Category.Enabled = True
        cbo_Status.Enabled = True

Hope this helps.