Macro to Rewrite Legend Lables 1

[logius]

I'm trying to figure out a way of changing the Series labels in a given chart (the lables located in the legend) using a macro. What I want to do is replace them with information (names) located within a particular set of cells. Does anyone know how I can access the names values of the legend elements?

 

[Mala]

I assume the following
1. The name of the series you want to import datalabel labels for is "MySeries"
2. The labels you want to put for the points is in a col. and the first cell of this range is named 'Lbl1'
3. The Chart is an embedded chart in the active sheet with name "MyChart".

You'd need to use the following code:

Dim Ser as Series
ActiveSheet.ChartObjects("MyChart&quot.Activate
Set Ser=ActiveChart.Series("MySeries&quot
For n = 1 to Ser.Points.Count
With Ser.Points
.HasDatalabel = True
.DataLabel.Text= Range("Lbl1&quot.cells(n,1)
End with
Next n

This was written extempore - so u may have to tweak it some to get the correct usage of the methods/properties - but the meat of the logic is there all right.

GOOD LUCK

 

[logius]

Thanks for your help. It's not actually what I needed, but it gave me some good ideas. What I'm really looking for is a way to change the names in the legend. That way, instead of saying "SeriesX" it'll have the appropriate label (but only in the legend).

 

[Mala]

Sorry, I got it wrong... Be back soon.

 

[logius]

Thanks, Mala, you're a lifesaver. I figrued it out. What I wanted was the value in:

     ActiveChart.SeriesCollection(x).Name

Using that, I was able to change the names of the legend elements to whatever I wanted using a simple assignment command.

Ex:

 ActiveChart.SeriesCollection(2).Name = "Time Cost"

Using a simple FOR loop, I'm able to change the names now. Where you could still help, though... Now I need a way to find out how many Legend items there are. Anyone know of a Count method for this?

 

[Mala]

Logius, I went thru all the help available and came up dry - there's no direct way you can change the legend text for any series. The legend txt for any series CANNOT be anything other than the SeriesName.

So, u're right - just name each series according to the name u want to see in the Legend entry...

I share with you the joy of your discovery.