Filter Checkbox.Value

[jamescooper]

Hello, trying to use a checkbox to filter a sheet. The CheckBox3.Caption works, but when I add further filters, CheckBox4 and CheckBox5 it doesn't; any ideas please?

Private Sub CommandButton1_Click()

If OptionButton1.Value And OptionButton3.Value = True Then

Lastrow = Sheets("Data - Accidents").Cells(Rows.Count, "A").End(xlUp).Row

'Application.Visible = False

With Sheets("Data - Accidents")
            .Range("A1:AS" & Lastrow).AutoFilter Field:=8, Criteria1:="Employee"
            .Range("A1:AS" & Lastrow).AutoFilter Field:=45, Criteria1:=TextBox2
            If CheckBox3.Value = True Then
            .Range("A1:AS" & Lastrow).AutoFilter Field:=39, Criteria1:=CheckBox3.Caption
            If CheckBox4.Value = True Then
            .Range("A1:AS" & Lastrow).AutoFilter Field:=39, Criteria1:=CheckBox4.Caption
            If CheckBox5.Value = True Then
            .Range("A1:AS" & Lastrow).AutoFilter Field:=39, Criteria1:=CheckBox5.Caption

End If

End If

End If

End With

End If

End Sub

 

[Fluff]

Try stepping through the code with F8, that way you will see that if Ceckbox3 is false it never looks at the other two checkboxes.
Also (IMO) it is easier to see the "flow" of the code if properly indented, like

      With Sheets("Data - Accidents")
         .Range("A1:AS" & LastRow).AutoFilter Field:=8, Criteria1:="Employee"
         .Range("A1:AS" & LastRow).AutoFilter Field:=45, Criteria1:=TextBox2
         If CheckBox3.Value = True Then
            .Range("A1:AS" & LastRow).AutoFilter Field:=39, Criteria1:=CheckBox3.Caption
            If CheckBox4.Value = True Then
               .Range("A1:AS" & LastRow).AutoFilter Field:=39, Criteria1:=CheckBox4.Caption
               If CheckBox5.Value = True Then
                  .Range("A1:AS" & LastRow).AutoFilter Field:=39, Criteria1:=CheckBox5.Caption
               End If
            End If
         End If
      End With

That said, will you only ever have one checkbox selected?

 

[jamescooper]

Thanks Fluff - Yes, it could be 1, or 2 or 3?

 

[Fluff]

Ok, how about

Private Sub CommandButton1_Click()
   Dim Ary(1 To 3) As Variant
   Dim i As Long
   Dim Flg As Boolean
   
   If OptionButton1.Value And OptionButton3.Value = True Then
      For i = 1 To 3
         If Me.Controls("CheckBox" & i) Then
            Ary(i) = Me.Controls("Checkbox" & i).Caption
            Flg = True
         End If
      Next i
      LastRow = Sheets("Data - Accidents").Cells(Rows.Count, "A").End(xlUp).Row
      
      'Application.Visible = False
      With Sheets("Data - Accidents")
         .Range("A1:AS" & LastRow).AutoFilter Field:=8, Criteria1:="cumbria" '"Employee"
         .Range("A1:AS" & LastRow).AutoFilter Field:=45, Criteria1:=TextBox2
         If Flg Then
            .Range("A1:AS" & LastRow).AutoFilter 39, Ary, xlFilterValues
         Else
            .Range("A1:AS" & LastRow).AutoFilter 39
         End If
      End With
   End If

End Sub

 

[jamescooper]

Failing on this code: "Could not find the specified object."

         If Me.Controls("CheckBox" & i) Then

 

[Fluff]

Oops, your checkboxes are 3 to 5 not 1 to 3. Try

      For i = 3 To 5
         If Me.Controls("CheckBox" & i) Then
            Ary(i - 2) = Me.Controls("Checkbox" & i).Caption
            Flg = True
         End If
      Next i

 

[jamescooper]

That works well, thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback