Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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:' |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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:' |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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:' |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter by User ID | Excel Discussion (Misc queries) | |||
new pivot table user with filter question | Excel Discussion (Misc queries) | |||
User form to change manual field filter in pivot table | Excel Discussion (Misc queries) | |||
Filter Excel Pivot, based on user entry form | Excel Discussion (Misc queries) | |||
Restrict-Filter-Limit-Validate user input in Excel | Excel Discussion (Misc queries) |