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

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

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