Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart label - alter with autofilter (97)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart label - alter with autofilter (97)

    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).Criter ia1, 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?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart label - alter with autofilter (97)

    The calculate event should fire. If not, include a subtotal function that refers to the filtered range.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart label - alter with autofilter (97)

    That worked like a charm. Thanks much.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •