Referencing a Chart on a Worksheet
 

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

Referencing a Chart on a Worksheet
 

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

Referencing a Chart on a Worksheet
 

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