Redefining the Range for a SeriesCollection

[David Glotfelty]

I am working on a project where the size of the range
being plotted changes frequently. I want to be able to
use VBA to evaluate the Range change and adjust the
SeriesCollection/s accordingly. I can do this for
embedded (chartobjects) charts, but not stand alone
Charts. It seems that I can't make a reference to a range
while the chart is activated. IF the chart isn't
activated I can't have access to the series collection.

Any help would be appreciated.

Thank you,
David Glotfelty

 

[Jon Peltier]

David -

Two points. First, I almost always use dynamic charts based on dynamic
worksheet ranges for this kind of thing. It happens quickly, and if
your formulas are clever, you should be able to do anything that you
would also do in VBA. The only shortcoming with the non-VBA approach is
when the number of series also changes. I have a few dynamic chart
examples and a page full of links on my web site:

http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html

Second, in VBA, to specifically refer to a range, it is always safer to
also include which sheet the range resides on. If the active sheet is a
worksheet, you can get away with a

Range("A1:B10")

reference, but if you indicate the worksheet, it will work with a range
on the active worksheet, or on an inactive worksheet whether the active
sheet is a chart or a worksheet:

Worksheets("Sheet2").Range("A1:B10")

- Jon