Referencing a Chart on a Worksheet

[Guest]

Hello all,

I've got a problem that is driving me up the wall. All my searches have
proved fruitless, so I'm hoping someone smarter can answer my question, or
point me to a post that answers it.

I've trying to change the data source of a chart in VBA. All the previous
code used:
ActiveChart.SeriesCollection.Count

I'm trying to use an actual reference to the chart so it doesn't have to be
selected. What I can't do is reference the SeriesCollection of the chart.
Everything I try gets me a "subscript out of range" error.

It would look like this:
Worksheets(SHEETNAME).ChartObjects(1).Chart.SeriesCollection.Count

Or so I thought. This isn't a chart IN a worksheet, but a chart that IS a
worksheet. I can't find any way to point to the chart without selecting the
chart and using ActiveChart. Recording a macro isn't helping, it used
ActiveChart as well.

Any ideas you might have are appreciated. I'll try anything.

Thanks,
Jay

 

[Guest]

Always feel stupid replying to my own question...

If the chart IS the worksheet, I need to use:
Sheets(SHEETNAME).SeriesCollection.Count

If anyone has a comment on that, I'd love to hear it. I believe it's
because Worksheets doesn't include charts (as worksheets).

Thanks,
Jay

 

[Jon Peltier]

Semantics:

A worksheet is the kind of sheet with rows and columns and cells, plus
embedded objects like shapes, pictures, and charts.

A chart sheet is a sheet that contains just a chart (no grid), plus embedded
objects like shapes, pictures, and yes, even other charts.

- Jon