I have seven checkboxes on a cover sheet that change the values to true/false on a separate sheet (I am not sure the difference between the ActiveX checkboxes and the normal Design mode checkboxes). When I click a button to apply the filters (after the checkboxes are selected) the filter should make multiple filters based on the checkboxes. This filtering is done on a separate tab. I can only get it to do one filter at a time. Is there a way to do this without having a massive If statement for every single combination of checkboxes?
The greened out code only filters the last IF statement checked:
The non-greened code doesn't work. It runs but does not recognize the nameVar variables as having names in them.
Sub Product_Filter()
Dim nameVar1 As String
Dim nameVar2 As String
Dim nameVar3 As String
Dim nameVar4 As String
Dim nameVar5 As String
Dim nameVar6 As String
Dim nameVar7 As String
'nameVar1 = Worksheets("Cover").Cells(20, "D").Value
If Worksheets("Product").Range("A1").Value = 1 Then
nameVar1 = Worksheets("Product").Range("B1").Value
End If
If Worksheets("Product").Range("A2").Value = 1 Then
nameVar2 = Worksheets("Product").Range("B2").Value
End If
If Worksheets("Product").Range("A3").Value = 1 Then
nameVar3 = Worksheets("Product").Range("B3").Value
End If
If Worksheets("Product").Range("A4").Value = 1 Then
nameVar4 = Worksheets("Product").Range("B4").Value
End If
If Worksheets("Product").Range("A5").Value = 1 Then
nameVar5 = Worksheets("Product").Range("B5").Value
End If
If Worksheets("Product").Range("A6").Value = 1 Then
nameVar6 = Worksheets("Product").Range("B6").Value
End If
If Worksheets("Product").Range("A7").Value = 1 Then
nameVar7 = Worksheets("Product").Range("B7").Value
End If
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar1
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar2
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar3
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar4
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar5
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar6
Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar7
'If Worksheets("Product").Range("A1").Value = 1 Then
' nameVar1 = Worksheets("Product").Range("B1").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar1
' End If
'If Worksheets("Product").Range("A2").Value = 1 Then
' nameVar2 = Worksheets("Product").Range("B2").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
' nameVar2
' End If
'If Worksheets("Product").Range("A3").Value = 1 Then
' nameVar3 = Worksheets("Product").Range("B3").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar3
' End If
'If Worksheets("Product").Range("A4").Value = 1 Then
' nameVar4 = Worksheets("Product").Range("B4").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar4
' End If
'If Worksheets("Product").Range("A5").Value = 1 Then
' nameVar5 = Worksheets("Product").Range("B5").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar5
' End If
'If Worksheets("Product").Range("A6").Value = 1 Then
' nameVar6 = Worksheets("Product").Range("B6").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar6
' End If
'If Worksheets("Product").Range("A7").Value = 1 Then
' nameVar7 = Worksheets("Product").Range("B7").Value
' Worksheets("Raw").Range("$A$1:$R$13884").AutoFilter Field:=7, Criteria1:= _
nameVar7
' End If
End Sub
Bookmarks