Macro for naming a series in a chart

[paul061]

Hello, I have a macro for creating a chart. I'd like to have the series names come from a particular cell. I've had some help with some other things like this but can't seem to make them work the same. My programming is zero, so I've been shooting in the dark trying to make this work. This is what I've come up with but it doesn't work. I'd appreciate someone telling me how to make it work and why mine didn't work so I can begin to understand how this all is done. Thak you!

ActiveChart.SeriesCollection(3).Value = Range("B4").Value

 

[Andrew Poulsom]

The syntax is:

ActiveChart.SeriesCollection(3).Name = "='" & ActiveSheet.Name & "'!R4C2"

 

[paul061]

Thanks for the reply and sorry to seem stupid but if I want the contents of cell B7 to be the name for series 3 how would I write that?

 

[AlisonS]

Hi Paul,

This would work:

ActiveChart.SeriesCollection(3).Name= Range("B7").Value

Alison

 

[Andrew Poulsom]

B4 is Row 4 Column 2 (R4C2) so B7 would be R7C2.

 

[Andrew Poulsom]

Hi Paul,

This would work:

ActiveChart.SeriesCollection(3).Name= Range("B7").Value

Alison

Yes, but it wouldn't update if the contents of B7 changed.

 

[paul061]

Hmmm perhaps I'm missing something. This is what I have

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet9").Range("H19:L24"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(3).Name = Range("B7").Value
ActiveChart.SeriesCollection(4).Name = "=""Spec Min"""
ActiveChart.SeriesCollection(5).Name = "=""Spec Max"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet9"
With ActiveChart

Thanks for your help!

 

[Andrew Poulsom]

That will put a hard value in the Series' Name. My solution puts a formula there, so it will change if the contents of the referenced cell changes.

 

[AlisonS]

Paul,

Whichever reference you use, you need to move the chart back to the sheet first - it's automatically created on a chart sheet. Move the line marked with ## as below:

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet9").Range("H19:L24"), PlotBy _
:=xlRows
## ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet9" ##
ActiveChart.SeriesCollection(3).Name = Range("B7").Value
ActiveChart.SeriesCollection(4).Name = "=""Spec Min"""
ActiveChart.SeriesCollection(5).Name = "=""Spec Max"""

Alison

 

[paul061]

A hard value is fine. But it doesn't work. I'm getting this error
Runtime error 1004
Method range of object global failed