Removing unnamed Legend lines from an Excel template

[rdemyan]

I have created an Excel template with a maximum number of 10 series.
The worksheet is populated with series data from an Access application.

Everything works fine in terms of getting the data from Access to Excel
and then having the chart created.

The problem is that if 9 or fewer series are sent to the template, the
Legend still shows all ten series even though the chart shows only the
actual number of series. The "extra" series shown in the Legend do not
have a name.

I want to remove these 'extra' series from the Legend. I think it
would be easier to do this within the Excel template than to try to do
this from MS Access. In the Chart_Activate procedure

I tried looking for .SeriesCollection(i).Name = "" or
IsNull(.SeriesCollection(i).Name), but errors are generated in both
cases.

Then I thought, why not just trap the error and delete the unnamed
Legend item in the error handler using:
ActiveChart.Legend.LegendEntries(i).Delete

This actually worked, UNTIL, I activated the chart a second time. Then
I started getting an error because apparantly the series count actually
is ten even though only the number of series actually sent from Access
are charted.

Any thoughts on how I can reset the legend in the Excel template to
only display the actual number of series that are being plotted (even
though the template is set up to display ten).

Thanks.

 

[rdemyan]

Shane:

Thanks for the advice. I prefer VBA to macros, but have never done
this before in Excel. Do you have any examples you could point me to?

 

[ShaneDevenshire]

Try going to http://peltiertech.com/Excel/Charts/ChartIndex.html

And looking under the topic:
VBA Charting Techniques and Examples

 

[Jon Peltier]

I prefer VBA to macros

Excel macros are written in VBA.

- Jon

 

[rdemyan]

Thank you for pointing me to Jon's website. There appears to be a lot
of good information there which I'm sure will help me in many areas.

However, in doing more research on my issue, it appears that the
problem has to do with <blank series>. When looking under Source Data,
Series tab, there are multiple entries entitled <blank series>, I bet
if I can delete these, the corresponding legend items will go away.

Further research seems to indicate that these series are not easy to
delete using VBA. In cycling through the SeriesCollection, I can't
even figure out how to identify which series is a <blank series>.

Still, it would seem that if I can just delete (remove) these <blank
series>, that this would be the easiest solution to my problem.

Thanks.