Chart label – alter with autofilter (97)

Topic

New Reply

The worksheet has a simple barchart based on two columns. When I use the Data > Filter > Autofilter command to restrict the view to just some selected records, the chart also changes its view. Perfect.

Next step is to fix up the chart title. A static title is not useful, because it doesn’t respond to the autofilter. Likewise, a title linked to a specific cell is not appropriate because the link is static, and doesn’t change with the autofilter. How can I get the title to change when I change the autofilter?

Thanks for any help.

Reply | Quote

Replies

Partway there with this code attached to the SelectionChanged event of the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim sFilter As String
sFilter = Mid(ActiveSheet.AutoFilter.Filters.Item(12).Criteria1, 2)
Range(“chartlabel”).Value = “‘” & sFilter
End Sub

Is there an event that fires when the AutoFilter is applied that I can use instead of the SelectionChanged?

Reply | Quote

The calculate event should fire. If not, include a subtotal function that refers to the filtered range.

Reply | Quote

That worked like a charm. Thanks much.

Reply | Quote