Cannot change SeriesCollection.Values! Why not?

[Parity]

Hello there,

I've added a chart (point XY) to my worksheet with has a data series i
it. I am used to change the properties of that series collection with a
VBA macro like the following:


Code
-------------------

ActiveChart.SeriesCollection(1).XValues = "B2:B20"
ActiveChart.SeriesCollection(1).Values = "D220"

-------------------


This works fine for me as long as there are visible datapoints of tha
series collection in the chart. If I set the source range of thi
collection to an area where there are no data points, my main proble
occours.
I cannot access the propertys of the series collection anymore with VB
macros. Therefore I cannot set the source range back to an area wit
contains datapoints.
I have already tried to delete the series and add a new one like:


Code
-------------------

ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries

-------------------


But I cannot use the method Delete on the "empty" series collectio
either.
It seems like there is no series collection anymore (for VBA) if i
doesn't contain data points, but in Excel itself it is still present.

How can I fix this problem and change the porperties of a serie
collection, if the .Value-range does not have a data point in it?

I hope I could make myself clear, because I'm not a native englis
speaker.

Thanks in advance, Parit

 

[okaizawa]

Hi,

How about temporarily changing options, like:

AciveChart.DisplayBlanksAs = xlZero
AciveChart.PlotVisibleOnly = False

See
http://groups-beta.google.com/group...read/thread/b1631f1474a703f4/86a4b778040bc0e5


The following code dosen't work in my pc(Excel 2000).

ActiveChart.SeriesCollection(1).XValues = "B2:B20"
ActiveChart.SeriesCollection(1).Values = "D220"

This works:
ActiveChart.SeriesCollection(1).XValues = Sheets("Sheet1").Range("B2:B20")
ActiveChart.SeriesCollection(1).Values = Sheets("Sheet1").Range("D220")

 

[Tom Ogilvy]

You can test whether VBA considers whether the series exists or not with

If ActiveChart.SeriesCollection.Count > 0 then
or just to test

msgbox ActiveChart.SeriesCollection.Count

 

[Jon Peltier]

Some chart types seem to vanish when there is nothing to plot. You can
temporarily change the series to an area chart, make your changes to the
source data, then change back. Use the macro recorder to get the syntax.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______