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.

Dennis wrote:
>
> 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

[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)


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:4233264A.46494403@netscapeXSPAM.com...
> 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.
>
> Dennis wrote:
> >
> > 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

[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 wrote:
>
> 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)
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:4233264A.46494403@netscapeXSPAM.com...
> > 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.
> >
> > Dennis wrote:
> > >
> > > 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

--

Dave Peterson

[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" <ec35720@netscapeXSPAM.com> wrote in message
news:42333105.EB74664D@netscapeXSPAM.com...
> 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 wrote:
> >
> > 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)
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > news:4233264A.46494403@netscapeXSPAM.com...
> > > 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.
> > >
> > > Dennis wrote:
> > > >
> > > > 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
>
> --
>
> Dave Peterson

[Dave Peterson]

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

Bob Phillips wrote:
>
> 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" <ec35720@netscapeXSPAM.com> wrote in message
> news:42333105.EB74664D@netscapeXSPAM.com...
> > 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 wrote:
> > >
> > > 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)
> > >
> > > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > > news:4233264A.46494403@netscapeXSPAM.com...
> > > > 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.
> > > >
> > > > Dennis wrote:
> > > > >
> > > > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Bob Phillips]

You could add
Application.Volatile
at the start.

--

HTH

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


"Dennis" <Dennis@discussions.microsoft.com> wrote in message
news:73D83A15-BC7C-4C31-B612-6659328A8D05@microsoft.com...
> 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

[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)


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eg8fliyJFHA.2936@TK2MSFTNGP15.phx.gbl...
> You could add
> Application.Volatile
> at the start.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dennis" <Dennis@discussions.microsoft.com> wrote in message
> news:73D83A15-BC7C-4C31-B612-6659328A8D05@microsoft.com...
> > 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
>
>