Auofilter in excel (Active or Not)

[8177]

Hi Guys
I need to find out if the autofilter is On or Not I have tried

Public Sub t()
Sheets("Effort").Select
If AutoFilter.Active Then
MsgBox "On"
Else
MsgBox "Not On"
End If
End Sub


also

Public Sub t()
Sheets("Effort").Select
If AutoFilter = True Then
MsgBox "On"
Else
MsgBox "Not On"
End If
End Sub

No Joy

Any Ideas

 

[PHV]

You may try something like this:
If Sheets("Effort").AutoFilterMode Then
MsgBox "On"
Else
MsgBox "Not On"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

I usually make things like this into seperate functions (i.e. test if workbook is open, test if worksheet is created, etc)...

Function IsAutoFilterOn(Optional wks As Worksheet) As Boolean
    If ActiveWorkbook Is Nothing Then Exit Function
    If wks Is Nothing And Not ActiveSheet Is Nothing Then Set wks = ActiveSheet
    IsAutoFilterOn = wks.AutoFilterMode
End Function[/code[

You can test in various ways such as ..

[code]Sub TestIAFO()
    MsgBox IsAutoFilterOn
End Sub
Sub TestIAFO2()
    MsgBox IsAutoFilterOn(Sheets("Sheet1"))
End Sub
Sub TestIAFO3()
    MsgBox IsAutoFilterOn(Workbooks("Book1.xls").Sheets("Sheet1"))
End Sub
Sub TestIAFO4()
    If IsAutoFilterOn = True Then
        MsgBox "Yupper, it's on!"
    Else
        MsgBox "Nope, not on."
    End If
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[8177]

Cheers Guys thanks for your help they work great