Modify chart legend (series name) from vba

[ssakthivel]

I have a couple of charts in my worksheet. I am trying to modify the chart titles and the chart legends through vba. I am able to access and update chart titles. However legend series names are inaccessible. I guess I am not doing something right. Here is the simplified version of my code:

    Dim o As ChartObject
    Dim se As Series
    For Each o In mySheet.ChartObjects
        'Chart Title
        rngChartName = o.Chart.ChartTitle.Text
        o.Chart.ChartTitle.Text = rngNewChartName.Value
        'Legend
        For Each se In o.Chart.SeriesCollection
            rngSerialName = se.Name
            se.Name = rngLegendNewName
            'Advance the ranges
            Set rngSerialName = rngSerialName.Offset(1, 0)
            Set rngLegendNewName = rngLegendNewName.Offset(1, 0)
        Next
    Next

In the watch window, se.Name is <unable to="" get="" the="" name="" property="" of="" series="" class=""><unable to="" get="" the="" name="" property="" of="" series="" class="">"Unable to get the Name property of the Series class".

I tried accessing se.Name after activating the chart by including the line o.Activate and o.Chart.Activate hoping that might help. Actually I tried o.Activate first and then tried o.Chart.Activate. No dice.

Can anyone see what I am missing in the above code?

Thanks</unable></unable>

 

[pgc01]

In the watch window, se.Name is <UNABLE series="" of="" property="" name="" the="" get="" to=""><UNABLE series="" of="" property="" name="" the="" get="" to="">"Unable to get the Name property of the Series class".

I tried accessing se.Name after activating the chart by including the line o.Activate and o.Chart.Activate hoping that might help. Actually I tried o.Activate first and then tried o.Chart.Activate. No dice.
</UNABLE></UNABLE>

Hi

You shouldn't need to activate or select anything.

To get the series names, try this:

Sub Test()
Dim o As ChartObject
Dim se As Series
 
For Each o In Worksheets("Sheet1").ChartObjects
    For Each se In o.Chart.SeriesCollection
        MsgBox o.Chart.Name & ": " & se.Name
    Next
Next
End Sub

Remark: there are several variable declarations missing in your code. You should write "Option Explicit" in the first line of the module to make sure that all the variables are declared.

 

[ssakthivel]

Thanks pgc01 for the reply. You have simplified my code further which I tried. I am still getting run time error "Unable to get the name property of the series collection" on this line MsgBox se.Name

The sheet is not protected. I am not sure what else I might be missing. Any other thoughts?

[Oh BTW, all the variables in my code snippet are declared. I had failed to bring them here.]