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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

L Mehl wrote:
> 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
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004
>
>

Hi Jon --

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

Larry


"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______
>
> L Mehl wrote:
> > 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
> >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004
> >
> >
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______
>
> L Mehl wrote:
> > 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
> >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004
> >
> >
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

L Mehl wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>http://PeltierTech.com/Excel/Charts/
>>_______
>>
>>L Mehl wrote:
>>
>>>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
>>>
>>>
>>>
>>>---
>>>Outgoing mail is certified Virus Free.
>>>Checked by AVG anti-virus system (http://www.grisoft.com).
>>>Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004
>>>
>>>
>>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004
>
>