CODEWITHSUNDEEP
excelvba
Michael Liew
Michael Liew

Reputation: 337

Excel VBA filtering/unfiltering chart series

I have a sheet with several graphs in it. All the graphs have the same 3 sets of series. However sometimes there's no data for those series so I'm writing some code that will turn them on/off (so that there's no legend in the chart for an empty series)

To simplify I've take snippet of my code that's causing issues

Dim myChart As Chart
Set myChart = shIndustry.Shapes("IndustryChart702").Chart

myChart.SeriesCollection("Correlation").IsFiltered = True
myChart.SeriesCollection("Crowding").IsFiltered = True

myChart.SeriesCollection("Correlation").IsFiltered = False
myChart.SeriesCollection("Crowding").IsFiltered = False

My full code does some checking to see if the series needs to be filtered or not. But the trouble I have is once a series has been filtered (set to true)

I can't change the value or reference it anymore so the bottom two lines error out with "Parameter not Valid"

I tried using indexing as well SeriesCollection(1), SeriesCollection(2) etc but that didn't work either.

How do I set IsFiltered back to false after filtering it?

Many Thanks

Upvotes: 1

Views: 449

Answers (1)

JohnM
JohnM

Reputation: 3350

Replace SeriesCollection with FullSeriesCollection which, per the MS Docs "enables you to get a filtered out Series object and filter it back in". So, your code would become

Dim myChart As Chart
Set myChart = shIndustry.Shapes("IndustryChart702").Chart

myChart.FullSeriesCollection("Correlation").IsFiltered = True
myChart.FullSeriesCollection("Crowding").IsFiltered = True

myChart.FullSeriesCollection("Correlation").IsFiltered = False
myChart.FullSeriesCollection("Crowding").IsFiltered = False

I would suggest using FullSeriesCollection when also filtering-out so as to avoid possible bugs (e.g. accidentally filtering-out an already filtered-out series).

Upvotes: 2

Related Questions