Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
Hello,
I woudl like to run some code after a new selection has been made on an autofilter drop down and also get the value that the user selected in the auto filter. Is this possible Many thanks in advance. Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
Martin,
Tom Ogilvy has answered this one many times, so I will just copy his usual reply: 'Start of quoted message You could use a userdefined function to display it (or read it), such as this one: Public Function ShowFilter(rng As Range) As String 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 = CStr(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 Since a filter change wouldn't necessarily cause it to recalc, you can enter it like this: =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) B2 is the header for one of the columns in the filter. the function should be placed in a general module, not a sheet module. 'End of quote You can also use it like this myFilter = ShowFilter(Range("B2")) from within VBA code. HTH, Bernie MS Excel MVP "Martin" wrote in message ... Hello, I woudl like to run some code after a new selection has been made on an autofilter drop down and also get the value that the user selected in the auto filter. Is this possible Many thanks in advance. Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
Thank you, apologise for repeating the content.
Martin "Bernie Deitrick" wrote: Martin, Tom Ogilvy has answered this one many times, so I will just copy his usual reply: 'Start of quoted message You could use a userdefined function to display it (or read it), such as this one: Public Function ShowFilter(rng As Range) As String 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 = CStr(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 Since a filter change wouldn't necessarily cause it to recalc, you can enter it like this: =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) B2 is the header for one of the columns in the filter. the function should be placed in a general module, not a sheet module. 'End of quote You can also use it like this myFilter = ShowFilter(Range("B2")) from within VBA code. HTH, Bernie MS Excel MVP "Martin" wrote in message ... Hello, I woudl like to run some code after a new selection has been made on an autofilter drop down and also get the value that the user selected in the auto filter. Is this possible Many thanks in advance. Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |