Toggle filter on/off using VBA

[Jimbob2000]

I have some pretty simple code that just isn't working... Basically, I want to create a button at the head of a column to toggle a filter on and off. So, if I click the button when the filter isn't applied, it applies the filter; if I click the button when it is applied, the filter is removed.

When I run the macro, the filter applies for a split second then is taken off. I can't work out what's going on. I'm pretty new to using If statements, so maybe that's where I'm going wrong...?

Sub S12()

With ActiveSheet


If .Range("A2:AF5000").AutoFilter(field:=17, Criteria1:="x") = False Then
    .Range("A2:AF5000").AutoFilter field:=17, Criteria1:="x"
Else: .Range("A2:AF5000").AutoFilter field:=17
    
End If
End With


End Sub

 

[DanteAmor]

Try this

Sub S12()
  With ActiveSheet
    If .AutoFilter.Filters.Item(17).On Then
      .Range("A2:AF5000").AutoFilter Field:=17
    Else
      .Range("A2:AF5000").AutoFilter Field:=17, Criteria1:="x"
    End If
  End With
End Sub

 

[Jimbob2000]

Thanks! It worked perfectly!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.