mardi 17 novembre 2020

VBA Filter based on Check Box Value

The following code Filters a sheet Column (filterColumn) by a String (filterEntry). Depending on the selection of two Check Boxes (CheckBoxNR1 & CheckBoxAI1) in a Userform this should then apply a secondary filter to Column E.

No matter what the value of the two Check Boxes (CheckBoxNR1 & CheckBoxAI1) this always returns to filter out "NEEDS RESOURCE" and " AWAITING INFO" from Column E.

However what I am trying to achieve is:

"NEEDS RESOURCE" is only filtered out if CheckBoxNR1 = False

"AWAITING INFO" is only filtered out if CheckBoxAI1 = False

When both Check Boxes = True then no filters should be applied to Column E.

    If TeamReportBtnClick = True Then
        Sheets("Data_Sheet").Range("A1").CurrentRegion.AutoFilter Field:=filterColumn, Criteria1:=filterEntry
            If CheckBoxAI1 = False And CheckBoxNR1 = False Then
                Sheets("Data_Sheet").Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:="<>NEEDS RESOURCE", Criteria2:="<>AWAITING INFO", Operator:=xlAnd
            ElseIf CheckBoxAI1 = True And CheckBoxNR1 = False Then
                Sheets("Data_Sheet").Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:="<>NEEDS RESOURCE"
            ElseIf CheckBoxAI1 = False And CheckBoxNR1 = True Then
                Sheets("Data_Sheet").Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:="<>AWAITING INFO"
            End If
    End If



Aucun commentaire:

Enregistrer un commentaire