Check if Autofilter is on when opening workbook, if it is off, then turn it on

[sspatriots]

Hi,

I think this should be easy, but the more I tinker with it, I get the same results. The code below is called from the workbook open event. I want it to see if "Autofilter" is turned on for my "table1". If it is on, I want it to stay on, if it is off, I want it turned on. The code I have below just turns it on if it is off or turns it on if it is on when I open the workbook. Please help.


Thanks, SS

Sub AutoFilterCheck()

    If ActiveSheet.AutoFilterMode = True Then
    
        MsgBox "Auto Filter is turned on"

    Else
    
        MsgBox "Auto Filter is turned off"
   
        Worksheets("PO History").ListObjects("Table1").Range.AutoFilter
        
    End If
    
End Sub

 

[DanteAmor]

This instruction turns it on if it is off and if it is on it turns it off

Worksheets("PO History").ListObjects("Table1").Range.AutoFilter

Try this:

Sub AutoFilterCheck()
  Dim tbl As ListObject
  Dim st As Boolean
  
  Set tbl = Worksheets("PO History").ListObjects("Table1")
  On Error GoTo turn_on_filter
  st = tbl.AutoFilter.FilterMode
    MsgBox "Auto Filter is turned on"
    
  Exit Sub
  
turn_on_filter:
  MsgBox "Auto Filter is turned off"
  tbl.Range.AutoFilter

End Sub

 

[sspatriots]

That's perfect. Thanks so much...