How to make Bullen's FilterCriteria() data refresh real-time?

[Dennis]

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria() Function.

That said, apparently the only way to refresh the data in the display cell
is to re-activate the formula by pressing <ENTER> in the Fx Box.

Can the display cells be made to refresh in real-time or, second best, by F9?

FYI (Background Info):

************************************************************II placed this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
*************************************************************
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
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
*************************************************************

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called" for
each refresh?

TIA Dennis

 

[Dave Peterson]

You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate until excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

 

[Bob Phillips]

You could add
Application.Volatile
at the start.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

But changing the filter criteria forces a recalc, and that is what drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

BTW, I would make this slight amendment so that it doesn't show blank when
no filter is applied (personal preference)

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Application.Volatile
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
Filter = "All"
GoTo Finish
End If
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



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I used xl2003 and did Data|Filter|showall (slightly different than changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

 

[Bob Phillips]

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it

Regards

Bob

BTW I liked the TEXT technique.

 

[Dave Peterson]

Did Data|filter|Showall cause a recalc in the version you're using?

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it

Regards

Bob

BTW I liked the TEXT technique.

 

[Bob Phillips]

Lol. I assumed the Showall was a 2003 new feature. I have never used it
myself, I always click the dropdown and select (All).

You are correct though, it doesn't force a recalc. I find that a bit odd, as
the way that I do it does force a recalc?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Strange <> surprising in excel.
<vbg>

I actually added the "show all" button to a toolbar. I was tired of going to
each filter and choosing All (or clicking on data|Filter|showall) each time I
wanted to see, er, all the data.

Lol. I assumed the Showall was a 2003 new feature. I have never used it
myself, I always click the dropdown and select (All).

You are correct though, it doesn't force a recalc. I find that a bit odd, as
the way that I do it does force a recalc?

--

HTH

RP
(remove nothere from the email address if mailing direct)