VBA: How to check if a ListObject (Table) is being filtered on a specific field

[Chris The Rock]

I'm editing a macro that came in a template downloaded from Microsoft.

I want it to do two new things when a cell is double-clicked, provided the double-clicked cell is in the column I want filtered:
1) Filter that table on the value in the double-clicked cell.
2) UN-filter that table on the value in the double-clicked cell, if the table's been filtered on that field.

I can't figure out what the syntax would be to check if the table has been filtered on a given field. All I can find is how to check if the table's been filtered on ANY field, and that won't work. I think once I know how to check that, I can design the rest of the changes in the macro.

I have the first part done. I can filter on a double clicked cell, but I want the user to be able to un-filter without having to know how to use the Autofilter controls.

Here's the code, mostly ripped off from the template:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    On Error Resume Next
    
    With Application
        .Cursor = xlNorthwestArrow
        BooleanCellDoubleClick Target, [Tasks[[Done]]], Cancel
        .Cursor = xlDefault
    End With
    
    If Intersect(Target.EntireRow, [Tasks[Owner]]) Is Nothing Then Exit Sub
    If Target.Cells.Count <> 1 Then Exit Sub
    
   'Check if double-clicked cell is the criteria for the filter on the Owner field
   'uh......how do I know if the table's been filtered on Field 6?
    
   'If there's no filter in place, filter based on the double-clicked cell
    With Sheet2
        .ListObjects(1).Range.AutoFilter Field:=6, Criteria1:=Intersect(Target.EntireRow, [Tasks[Owner]])
    End With
    Cancel = True
       
End Sub

 

[Chris The Rock]

Figured it out: I looked through the object model until I found something I could reference.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lo As ListObject
Dim i As Integer

Set lo = Sheet2.ListObjects("Tasks")

    On Error Resume Next
    
    With Application
        .Cursor = xlNorthwestArrow
        BooleanCellDoubleClick Target, [Tasks[[Done]]], Cancel
        .Cursor = xlDefault
    End With
    
    If Intersect(Target.EntireRow, [Tasks[Owner]]) Is Nothing Then Exit Sub
    If Target.Cells.Count <> 1 Then Exit Sub
    
   'Check if double-clicked cell is the criteria for the filter on the Owner field (6)
    If lo.AutoFilter.Filters(6).On Then
    lo.Range.AutoFilter Field:=6
        Exit Sub
    End If
           
   'If there's no filter in place, filter based on the double-clicked cell
    lo.Range.AutoFilter Field:=6, Criteria1:=Intersect(Target.EntireRow, [Tasks[Owner]])

    Cancel = True
       
End Sub