Results 1 to 2 of 2

Thread: Excel365 64-Bit - Filter removes SeriesCollection from Chart

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Excel365 64-Bit - Filter removes SeriesCollection from Chart

    Hi Folks,

    noticed something "weird"

    I have a workbook with two sheets
    Sheet 1 has a "standard" Worksheet with regular columns and rows, conditional formatting, formulas and what not (what you would expect from a business report regarding Sales)
    On Sheet 2 i have a Chart which is fed via vba-macro everytime the user clicks in another cell/row (it doesn't matter where in the row the user clicks) via Sheet1_SelectionChange-Event
    the vba-macro just updates the Formula of the Chart
    Code:
    chtForecast.SeriesCollection(1).FormulaLocal = "=SomeFunctionBlablabla"
    This works perfectly (and yes, there is some other code in there, but that code has been debugged ad nauseum)

    BUT: The Moment the User uses the (Auto-) Filters in the column-headers to reduce the rows (say "Show me all rows where Sales-Value<1000 €") the SeriesCollection disappears visibly from the Chart.
    The Chart-Object itself is still there.

    Weirdness 2: If the User removes the Filter (and it doesn't matter if it's only one filter applied, or more (in other columns)) the SeriesCollection is back again

    And no, applying the Filter doesn't trigger the Selection_Changed-Event (already checked).
    I've inserted a simple Debug.Print line in the Sheet_Calculate-event printing the SeriesCollection.Count, and voila: it prints zero when filtered, 1 when Filter(s) removed


    HUH?
    Last edited by Zvoni; Sep 16th, 2022 at 04:25 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel365 64-Bit - Filter removes SeriesCollection from Chart

    Quick update:
    I've kinda "solved" it by adding a line to the vba-code
    Code:
    Dim ns As Series
    .
    .
    'Delete all Series from Collection in a loop
    .
    .
    'Later
    Set ns = MyChart.SeriesCollection.NewSeries
    Now the Chart updates/displays correctly even when source-sheet is filtered, but i'd still like to know why a Filter removes all Series
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width