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 |
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 |
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 |
All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com