Changing a series' Name, X- and Y-values by using its series number

[L Mehl]

I can change the name, X- and Y-values in series #1 with the following code:

ActiveChart.SeriesCollection(1).Name = _
strNameSeries
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("Data_Series").Range(strRangeSeries)
ActiveChart.SeriesCollection(1).Values = _
Worksheets("Data_Series").Range(strRangeYVal)

but I can't revise series #3, when I change
SeriesCollection(1)
to
SeriesCollection(3) in the above code.

I get the error: "Unable to get the Name property ..."

The third series shows a Name and X- and Y-values in the Source Data|Series
dialog box.
Can someone tell me why I get this error?


Understanding this is important for me because I will provide the user with
a template Chart containing 9 dummy series, as placeholders for real data.

The user will import a text file into one of the 9 dummy series, changing
the Name, X- and Y-values.

Is it correct to ask the user which order number is to be replaced, and use
this in code as above?

If not correct, can someone describe how to address the series for which I
want to change Name, X- and Y-values?

Thanks for any suggestions.

Larry Mehl

 

[Jon Peltier]

Larry -

VBA isn't quite as smart as the Excel interface when dealing with
charts. In this case, if the series isn't plotted (it's all blanks or
errors), you can access the ranges in the manual user interface, but you
can't get to the elements of the series formula.

There are two workarounds. One is, change the chart type of the
offending series to an area chart first, adjust the .Name, .Values, and
..XValues of the series, then change the chart type back. The other,
which I *think* works (but I'm not able to test it right now) is to
change the entire series formula in one stroke, which means building up
the string, and using SeriesCollection(3).SeriesFormula = mySrsFmla.

- Jon

 

[L Mehl]

Hi Jon --

Thank you for the suggestions. It is comforting to know I was not going
crazy.

Larry

 

[L Mehl]

Jon --

It looks like your suggestion of temporarily changing the chart type works.

I never would have stumled onto that method/trick.

I appreciate all the help you have given me in my charting project.

Larry

 

[Jon Peltier]

Larry -

I never would have stumbled on it either. But fellow MVP Bill Manville
told me about this behavior of Area charts, and how he used it in his
FindLinks utility to get the range references in an otherwise
unresponsive chart series.

- Jon