autorefresh display autofilter criteria question

[drabbacs]

I came across a post earlier where the user asked how to
display the active criteria being used in an autofiltered-
list. They were directed to the following site.

http://j-walk.com/ss/excel/usertips/tip044.htm

I followed the instructions there. However, the displayed
criteria are only updated after I force a refresh (ctrl-
alt-F9). Is there a way to autoupdate without needing to
do it manually?

Thanks in advance

The code for the new module follows:

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo
Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

 

[Tom Ogilvy]

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

Should trigger it to update. Change B5:B200 to reflect the address of a
column in the filtered database.

 

[drabbacs]

Very nice. Thank-you.

Only one issue. If I use the 'show all' button on my
toolbar (I've done some toolbar customization), it doesn't
reflect the change. But if I go to a filtered column
dropdown and tell it 'All' then it will update.

This is really a minor issue to me. If you happen to have
a fix, I'd like to see it. If not, no big deal.

Thanks for your help.
Drabbacs