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.Series Collection.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

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

"Jay" wrote:

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.Series Collection.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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jay" wrote in message
...
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

"Jay" wrote:

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.Series Collection.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