Updating a graph title with autofilter criteria

Posted by Andy on July 30, 2001 9:02 AM

I have a large set of data that I display on a number of graphs. I have an Autofilter set up on the data. I want the title of the graph to reflect the Autofilter criteria I am currently selecting (e.g. "Plant number 1, Vessel number 6"). I know how to get a graph title to display from a worksheet cell, but how do I get the autofilter criteria to display in a cell ? I've even tried using a VB macro to no avail.

Help !!

Posted by Damon Ostrander on July 31, 2001 10:51 AM

Andy,

Here's a macro that assigns the activesheet's first embedded chart's title to the filter's first criteria. You could obviously apply the same idea to write the filter criteria into a cell instead of to the chart title.

You can either assign the chart or a button to this macro, so that a single click on the chart or the button updates the chart title from the current filter criteria, or you could use a Chart object Change event to automatically update the title each time the chart changes.

Since you can have multiple filters on a worksheet, I had the macro find the first filter that is On (filtering), but of course you can hardwire it for the particular filter that the chart title is dependent on.

Happy computing.

Damon

Here's the macro:

Sub UpdateChartTitle()
Dim F As String
Dim Fltr As Filter

For Each Fltr In ActiveSheet.AutoFilter.Filters
If Fltr.On Then Exit For
Next Fltr

If Fltr.On Then
F = Fltr.Criteria1
F = Right(F, Len(F) - 1) 'Strip off equal sign at beginning of string
Else
F = "All Criteria"
End If

ActiveSheet.ChartObjects(1).Chart.ChartTitle.Characters.Text = F

End Sub