How to find all charts on a sheet

[UHsoccer]

What is the VB codee to list all charts on a sheet?

 

[Glaswegian]

This assumes that the charts are embedded in a worksheet and lists their names and worksheet name in column A.

Sub FindCharts()
Dim cht As ChartObject
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
For Each cht In sht.ChartObjects
Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value = cht.Name & " is on " & sht.Name
Next cht
Next sht
End Sub

HTH

Regards

 

[UHsoccer]

Thanks got that to work

I am trying to set the name of each line. When I recorded a macro it looked like

ActiveChart.SeriesCollection(1).Name = "=""Gas"""

in Vb I use

ActiveChart.SeriesCollection(NumL).Name = "=" & nameProp & ""
where numL = 1 and nameProp = Gas (read from a cell)

Error is "Unable to set name property of Series class" (The chart is already on a sheet)

 

[Glaswegian]

Try this

ActiveChart.SeriesCollection(NumL).Name = nameProp

You've assigned the name to the variable so no need for the extra "".

Regards

 

[UHsoccer]

Thanks, I am able to write VB code, but am not to skilled using ideas like

Dim cht As ChartObject
Dim sht As Worksheet

So in general, my code is not as "elegant" and probably not as simple