Filtering data when Toogle button is on

[Guinaba]

Hello experts,

Have a toogle button in my sheet filtering a specific column (code below), the filtering criteria is cell (17,5).value. What I'd like to do is when the filter is still on if the cell (17,5).value is changed the filter automatically changes as well.

Private Sub ToggleButton1_Click()
   Dim LRow As Integer
   
  With ActiveSheet
  
    LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    
    If ToggleButton1.Value = True Then
      ToggleButton1.Caption = "Filter Item On"
      ToggleButton1.BackColor = vbGreen
      .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4, Criteria1:=.Cells(17, 5).Value
    Else
      ToggleButton1.Caption = "Filter Item Off"
      ToggleButton1.BackColor = vbWhite
      .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4
    End If
    
  End With

End Sub

 

[igold]

How about this:

In the Worksheet Module that has the ToggleButton1 on it, paste these two codes (it can be done with one large Copy/Paste)

Private Sub ToggleButton1_Click()

   Call FilterOn
   
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Target = Range("E17") Then Call FilterOn
    
End Sub

Then in another module of your choosing- "Module1" for example. Paste this code.

Sub FilterOn()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LRow As Integer
     
    With ws

        LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
      
        If ActiveSheet.ToggleButton1.Value = True Then
            ActiveSheet.ToggleButton1.Caption = "Filter Item On"
            ActiveSheet.ToggleButton1.BackColor = vbGreen
            .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4, Criteria1:=.Cells(17, 5).Value
        Else
            ActiveSheet.ToggleButton1.Caption = "Filter Item Off"
            ActiveSheet.ToggleButton1.BackColor = vbWhite
            .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4
        End If
      
    End With

End Sub

 

[Guinaba]

How about this:

In the Worksheet Module that has the ToggleButton1 on it, paste these two codes (it can be done with one large Copy/Paste)

Private Sub ToggleButton1_Click()

   Call FilterOn
  
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Target = Range("E17") Then Call FilterOn
   
End Sub

Then in another module of your choosing- "Module1" for example. Paste this code.

Sub FilterOn()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LRow As Integer
    
    With ws

        LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
     
        If ActiveSheet.ToggleButton1.Value = True Then
            ActiveSheet.ToggleButton1.Caption = "Filter Item On"
            ActiveSheet.ToggleButton1.BackColor = vbGreen
            .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4, Criteria1:=.Cells(17, 5).Value
        Else
            ActiveSheet.ToggleButton1.Caption = "Filter Item Off"
            ActiveSheet.ToggleButton1.BackColor = vbWhite
            .Range("$A$21:$AF$" & LRow).AutoFilter Field:=4
        End If
     
    End With

End Sub

Thanks @igold! It works perfectly!

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!