Flag 'Filter on' for the user
 

In Excel 2000, I want to create a 'flag' to alert the user whenever data is
filtered, and preferably tell the user what the filter is e.g. If a column
of dates is filtered on 'April', I would like the word 'April' to appear in
a separate cell in large letters and ideally on another worksheet, maybe
against a label that says 'currently filtered on:'

Flag 'Filter on' for the user
 

You could try Tom Ogilvy's ShowFilter UDF (below)

To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert Module
Copy and paste the UDF (below) into the white space on the right
[everything within the dotted lines]

Press Alt+Q to get back to Excel

In Excel,
Assume autofilter is applied on cols A to C in Sheet1

To use Tom's UDF, in Sheet2,
you could put in say B2: =showfilter(A1)
then copy B2 to D2 to display the autofilter selections made in Sheet1's A1:C1

And if you need to remove the "=" signs, you could use instead in B2:
=SUBSTITUTE(showfilter(Sheet1!A1),"=","")
Format font/size to taste

'---
Public Function ShowFilter(rng As Range)
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

Application.Volatile

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
'---

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"simon" wrote:
In Excel 2000, I want to create a 'flag' to alert the user whenever data is
filtered, and preferably tell the user what the filter is e.g. If a column
of dates is filtered on 'April', I would like the word 'April' to appear in
a separate cell in large letters and ideally on another worksheet, maybe
against a label that says 'currently filtered on:'

Flag 'Filter on' for the user
 

Excellent, thank you. I've not used VBE before

"Max" wrote:

You could try Tom Ogilvy's ShowFilter UDF (below)

To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert Module
Copy and paste the UDF (below) into the white space on the right
[everything within the dotted lines]

Press Alt+Q to get back to Excel

In Excel,
Assume autofilter is applied on cols A to C in Sheet1

To use Tom's UDF, in Sheet2,
you could put in say B2: =showfilter(A1)
then copy B2 to D2 to display the autofilter selections made in Sheet1's A1:C1

And if you need to remove the "=" signs, you could use instead in B2:
=SUBSTITUTE(showfilter(Sheet1!A1),"=","")
Format font/size to taste

'---
Public Function ShowFilter(rng As Range)
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

Application.Volatile

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
'---

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"simon" wrote:
In Excel 2000, I want to create a 'flag' to alert the user whenever data is
filtered, and preferably tell the user what the filter is e.g. If a column
of dates is filtered on 'April', I would like the word 'April' to appear in
a separate cell in large letters and ideally on another worksheet, maybe
against a label that says 'currently filtered on:'

Flag 'Filter on' for the user
 

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"simon" wrote in message
...

Excellent, thank you. I've not used VBE before

Flag 'Filter on' for the user
 

This may help but it is limited as it advises if any filtered columns on a
worksheet are in action
Apply the formula to a cell outside the any of your filtered columns. A5:A45
is the sample so change to suit your range.
=IF(COUNTA(A5:A45)=SUBTOTAL(3,A5:A45),"NO","YES")
You can Conditional Format the cell to turn RED for "YES" and GREEN FOR "NO"

You can also get fancy to highlight the Excel filter in action advice blue
arrow (hard to see)
Use a cell next the YES/NO cell and give it a heading "LOOK FOR"
Format the cell with formula to Wingdings 3 with blue color font format to
produce the same arrow
=IF(COUNTA(A5:A45)=SUBTOTAL(3,A5:A45),"","q")
Have fun!



"Max" wrote:

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"simon" wrote in message
...

Excellent, thank you. I've not used VBE before