list legend entries in a range

[ham.sajjadi]

hi every body.
i have a legend on my chart and i want to list them as text or number in a custom range such as ("ab1:ab17")
i write this snip.but i don't know what the problem is?i am noob

Sub Macro1()
'
' Macro1 Macro
'

'
Dim test As LegendEntries
Set test = ActiveSheet.ChartObjects("Charts1").Chart.legend
Range("ab1:ab17").Value = test

End Sub

thank u in advance.
here is my legend and chart (attached)

 

[GraH - Guido]

Hi Ham.Sajjadi,

That legend comes from somewhere doesn't it. So why not simply referring to the original source?

Otherwise I would not try to read out the legend, but refer to the data labels of the chart data ranges. But in your case you would need a for each loop. as well

  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.ChartTitle.Select
  ActiveChart.PlotArea.Select
  Application.CutCopyMode = False
  LegendEntry = ActiveChart.FullSeriesCollection(1).Name 
  Range("B1") = LegendEntry

Cheers
Guido

 

[ham.sajjadi]

thank

Hi Ham.Sajjadi,

That legend comes from somewhere doesn't it. So why not simply referring to the original source?

Otherwise I would not try to read out the legend, but refer to the data labels of the chart data ranges. But in your case you would need a for each loop. as well

  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.ChartTitle.Select
  ActiveChart.PlotArea.Select
  Application.CutCopyMode = False
  LegendEntry = ActiveChart.FullSeriesCollection(1).Name
  Range("B1") = LegendEntry

Cheers
Guido

u so much.
my data comes from data model and pover pivot.indeed,my chart is made from pivot chart.
sorry.but your snip code returns only one legend.i delete this row

  ActiveChart.ChartTitle.Select

because vba won't run unless i delete it

 

[GraH - Guido]

Hi ham.sajjadi,

Indeed it seems I left some test code in the solution I posted.

Here is a clean code (I hope ) that also iterates trough all the data series.

Sub GetDateSeriesNames()
dim i as integer
dim Var as variant
dim LegendEntry as string
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.PlotArea.Select
  i = 0
  For Each Var In ActiveChart.FullSeriesCollection
  i = i + 1
  Application.CutCopyMode = False
  LegendEntry = ActiveChart.FullSeriesCollection(i).Name
  Range("B" & i) = LegendEntry
  Next Var
End Sub

 

[ham.sajjadi]

Hi ham.sajjadi,

Indeed it seems I left some test code in the solution I posted.

Here is a clean code (I hope ) that also iterates trough all the data series.

Sub GetDateSeriesNames()
dim i as integer
dim Var as variant
dim LegendEntry as string
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.PlotArea.Select
  i = 0
  For Each Var In ActiveChart.FullSeriesCollection
  i = i + 1
  Application.CutCopyMode = False
  LegendEntry = ActiveChart.FullSeriesCollection(i).Name
  Range("B" & i) = LegendEntry
  Next Var
End Sub

YES!!it is working.
i really appreciate that.
another question.is there a way to list that legend automatically whenever data legend changed?
Indeed i use power pivot and slicer for my dashboard.so,when i change the filter,the legend on the chart changed also.

 

[ham.sajjadi]

Hi ham.sajjadi,

Indeed it seems I left some test code in the solution I posted.

Here is a clean code (I hope ) that also iterates trough all the data series.

Sub GetDateSeriesNames()
dim i as integer
dim Var as variant
dim LegendEntry as string
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.PlotArea.Select
  i = 0
  For Each Var In ActiveChart.FullSeriesCollection
  i = i + 1
  Application.CutCopyMode = False
  LegendEntry = ActiveChart.FullSeriesCollection(i).Name
  Range("B" & i) = LegendEntry
  Next Var
End Sub

hi.thank u.how can i list axis label instead of series??

 

[GraH - Guido]

hi.thank u.how can i list axis label instead of series??

Would not know if that is possible.

 

[GraH - Guido]

YES!!it is working.
i really appreciate that.
another question.is there a way to list that legend automatically whenever data legend changed?
Indeed i use power pivot and slicer for my dashboard.so,when i change the filter,the legend on the chart changed also.

I know it is possible via an on change event and then call the code. But I don't master that.