Has AutoFiltering Been Applied?

[Gary''s Student]

I need a simple Boolean function (UDF):

=isfilted(A1)

That will return TRUE if AutoFiltering has been applied to A1, otherwise
FALSE.

Thanks in advance

 

[Mike H]

Try,

Function IsFiltered(MyRange As Range) As String
Application.Volatile
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then GoTo GetMeOut
With .Filters(MyRange.Column - .Range.Column + 1)
If Not .On Then
IsFiltered = False
Else
IsFiltered = True
End If
End With
End With
GetMeOut:
End Function

Mike

 

[Mike H]

Maybe better now works even if no autofilter exists

Function IsFiltered(MyRange As Range) As String
Application.Volatile
On Error GoTo GetMeOut
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then GoTo GetMeOut
With .Filters(MyRange.Column - .Range.Column + 1)
If Not .On Then
IsFiltered = False
Else
IsFiltered = True
End If
End With
End With
Exit Function
GetMeOut:
IsFiltered = False
End Function

Mike

 

[Gary''s Student]

First, thank you for your response.

Your function responds to the STATE of the AutoFilter:

1. if the user selects one of the autofilter options, the function returns
True
2. if the user selects (All), the function return False

I need something a litle different. I need the function to return True once
the user has switched on Auto filtering for the cell in question thru
something like:

Data > Filter > Autofilter

You see, I don't care which option the user may choose from the filter
pull-down, only that filtering has been enabled and the pull-down can be used.

In any case, the code you posted is valuable and I will use it in the future.

Thanks for the help.

 

[Jim Cone]

Gary,
"ActiveSheet.AutoFilterMode" should do it.
--
Jim Cone
Portland, Oregon USA



"Gary''s Student"
wrote in message
-snip-
You see, I don't care which option the user may choose from the filter
pull-down, only that filtering has been enabled and the pull-down can be used.

 

[Mike H]

I see what you mean, it's easy to detect at the worksheet level but on a
single cell now that's a poser. Il be interested if this one gets solved.

Mike

 

[Gary''s Student]

Actually, your second post gave me the clue I needed:

Function isfilt(r As Range) As Boolean
Application.Volatile
isfilt = False
On Error GoTo GetMeOut
Set rr = r.Parent.AutoFilter.Range
If Not Intersect(rr, r) Is Nothing Then
isfilt = True
End If
Exit Function

GetMeOut:
End Function


Thanks again for your help.

 

[Mike H]

I could see that was the answer but couldn't get there.Nice one.

 

[Gary''s Student]

Thank you

 

[Cornflicks]

Hello Sir,
I don't know you but I want to pay someone to write a simple
accounting program using excel for my new business. I think it is easy for a
person familiar with excel programming. Please tell em if you can help. My
email (e-mail address removed)