To show the value that has been selected in the AutoFilter dropdown, you
can create a User Defined Function. Tom Ogilvy posted the following
function, that returns the criteria from a column in an autofiltered
table. It will show both criteria if there are two, and includes the
operator.
Paste the code (it's at the end of this message) onto a regular module
in your workbook. There are instructions here:
http://www.contextures.com/xlvba01.html
Use the function in a cell on your worksheet. To make it respond to a
filter change, tie it to the subtotal command. For example,
=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
would show the criteria for column B
Then, refer to this cell from your chart --
Select the chart
Click in the Formula bar, and type an equal sign
Click on the cell that contains the ShowFilter function, and it will be
referenced in the formula, e.g.:
='ChartData'!$H$2
Press the Enter key to complete the formula.
Point to the border of the text box that was created, and drag it to the
top of the chart. Format it with the buttons on the formatting toolbar.
'============================================
Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B
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
'==============================================
