Dear all,

I am using the below function to show the filter value. It works wel
for the first time filter. However, when I change the filter value, th
result of this function is unchanged until I go to the cell containin
this function and press F2 and then Enter, the result of this functio
is changed.

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

Is there any way that makes this function work without pressing F2 an
Enter when the value of filter is changed.

Thanks a lot!

Na

--
lehaina
-----------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...fo&userid=1861
View this thread: http://www.excelforum.com/showthread.php?threadid=47198

Tom Ogilvy has suggested this:

=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.



lehainam wrote:

Dear all,

I am using the below function to show the filter value. It works well
for the first time filter. However, when I change the filter value, the
result of this function is unchanged until I go to the cell containing
this function and press F2 and then Enter, the result of this function
is changed.

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

Is there any way that makes this function work without pressing F2 and
Enter when the value of filter is changed.

Thanks a lot!

Nam

--
lehainam
------------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...o&userid=18615
View this thread: http://www.excelforum.com/showthread...hreadid=471985

--

Dave Peterson

Dave, are suggesting adding this code to the function or using this to
call the function? TIA

Greg

The code wouldn't change.

But the formula in the cell would. (So I guess #2.)

GregR wrote:

Dave, are suggesting adding this code to the function or using this to
call the function? TIA

Greg

--

Dave Peterson