Cannot change SeriesCollection.Values! Why not?
 

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 = "D2:D20"

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


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

--
Parit
-----------------------------------------------------------------------
Parity's Profile: http://www.excelforum.com/member.php...fo&userid=2502
View this thread: http://www.excelforum.com/showthread.php?threadid=38553

Cannot change SeriesCollection.Values! Why not?
 

Hi,

How about temporarily changing options, like:

AciveChart.DisplayBlanksAs = xlZero
AciveChart.PlotVisibleOnly = False

See
http://groups-beta.google.com/group/...a4b778040bc0e5


The following code dosen't work in my pc(Excel 2000).
ActiveChart.SeriesCollection(1).XValues = "B2:B20"
ActiveChart.SeriesCollection(1).Values = "D2:D20"

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

--
HTH
okaizawa


Parity wrote:
Hello there,

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


Code:
--------------------

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

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


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

Cannot change SeriesCollection.Values! Why not?
 

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

--
Regards,
Tom Ogilvy

"Parity" wrote in
message ...

Hello there,

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


Code:
--------------------

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

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


This works fine for me as long as there are visible datapoints of that
series collection in the chart. If I set the source range of this
collection to an area where there are no data points, my main problem
occours.
I cannot access the propertys of the series collection anymore with VBA
macros. Therefore I cannot set the source range back to an area with
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 collection
either.
It seems like there is no series collection anymore (for VBA) if it
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 series
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 english
speaker.

Thanks in advance, Parity


--
Parity
------------------------------------------------------------------------
Parity's Profile:
http://www.excelforum.com/member.php...o&userid=25023
View this thread: http://www.excelforum.com/showthread...hreadid=385530

Cannot change SeriesCollection.Values! Why not?
 

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/
_______


Parity wrote:
Hello there,

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


Code:
--------------------

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

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


This works fine for me as long as there are visible datapoints of that
series collection in the chart. If I set the source range of this
collection to an area where there are no data points, my main problem
occours.
I cannot access the propertys of the series collection anymore with VBA
macros. Therefore I cannot set the source range back to an area with
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 collection
either.
It seems like there is no series collection anymore (for VBA) if it
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 series
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 english
speaker.

Thanks in advance, Parity