-
Sep 16th, 2022, 04:20 AM
#1
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
-
Sep 16th, 2022, 04:35 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|