Thread: Obtaining a pivot chart Legend name

Hello,

I have a pivot chart with a drop down menu thing (Legend?) on the right side of the chart (Excel labels this area "Series Field Names").

Can somebody please tell me how to get the name of that Legend?

I can get the names of the series values within the Legend via:
ActiveChart.SeriesCollection(i).Name

But what code would allow me to go one level up to determine the name of the Legend that these series values reside in? These two things don't work:
MsgBox ActiveChart.Legend.Name <= outputed "Legend"
MsgBox ActiveChart.SeriesCollection.Parent.Name <= outputed "Pareto Chart"

----
If a visual is easier, I posted in this forum earlier with a different question. In that example, I only have one of the drop down boxes on the right, and it is the "Car Color" Legend.

In my code, I'm basically trying to play around to create something like:

If LegendThingNameValue = "Car Color" Then
ActiveChart.SeriesCollection(1).Interior.ColorIndex = 5
End If

Thank you!
michael

Hi Michael,

Maybe someone else will come up with a better answer to this. But, I ran into the same blind alley that you did.

I am thinking that what you really want to know is ... did someone change the pivot table/chart? A way to determine this might be as follows:

Dim ch As Chart
    Set ch = ActiveChart

Dim pl As PivotLayout
    Set pl = ch.PivotLayout
 
    
Dim pt As PivotTable
    Set pt = pl.PivotTable
    
    MsgBox pt.TableRange1.Cells(1, 2)

Man, you are good! That's a smart solution. I was approaching this from the chart view and you went in and got the answer from the table view. I need to remember that for next time. Thanks for all your help!

Till next time...
michael