Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter
As many in our org can't distinguish which columns are
filtered (is it a blue or black triangle?), is there a manner in which that can (1) be made more prominent, maybe red or blinking, or (2) through VBA, display the text in the column in another cell -- I did try the Worksheet_SelectionChange(ByVal Target As Range), but clicking the filter "triangle" doesn't qualify as an selection change. Any feedback appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter
I don' t believe you can alter the built in functionality of the autofilter.
I don't understand what you mean by display text in the column in another cell? if you mean display the filter criteria in another cell, then yes, you could write a UDF to do that. 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 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 in a cell above the filter (as an example) put in the formula =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) The argument to showfilter designates what column. the Char(subtotal . . .. ) is used to force the showfilter to be recalculated when you change the autofilter criteria. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Chuck" wrote in message ... As many in our org can't distinguish which columns are filtered (is it a blue or black triangle?), is there a manner in which that can (1) be made more prominent, maybe red or blinking, or (2) through VBA, display the text in the column in another cell -- I did try the Worksheet_SelectionChange(ByVal Target As Range), but clicking the filter "triangle" doesn't qualify as an selection change. Any feedback appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |