Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 4th 05, 12:36 AM
Cheese
 
Posts: n/a
Default Help with ShowFilter macro

I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of
post). It says to use...

=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)

....in a cell to show the criteria for Column B. For one thing, I don't
understand the cells B2 and B3 business. What is supposed to be in those
cells?

I would like this function to appear in the cell directly above or below the
Autofilter menu cell. How do I change the function if the Autofilter criteria
menu is in, cell A3, for example, and I want the criteria (this function) to
appear just above, in cell A2?

After trying all sorts of things in different cells, the only thing I would
get is the #VALUE error.

Thanks for your help.


Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function


  #2   Report Post  
Old August 4th 05, 09:55 AM
Bob Phillips
 
Posts: n/a
Default

See my response in .misc

--
HTH

Bob Phillips

"Cheese" wrote in message
...
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of
post). It says to use...

=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)

...in a cell to show the criteria for Column B. For one thing, I don't
understand the cells B2 and B3 business. What is supposed to be in those
cells?

I would like this function to appear in the cell directly above or below

the
Autofilter menu cell. How do I change the function if the Autofilter

criteria
menu is in, cell A3, for example, and I want the criteria (this function)

to
appear just above, in cell A2?

After trying all sorts of things in different cells, the only thing I

would
get is the #VALUE error.

Thanks for your help.


Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro with F9 Kenny Excel Discussion (Misc queries) 1 August 3rd 05 02:41 PM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 11:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 01:40 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 11:43 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017