Reference an existing pivot chart by name

Question

I have a multiple pivot charts on a worksheet. How can I refer to a specific existing pivot chart by name? I currently have the following code that works:

ThisWorkbook.Sheets("State").Shapes.AddChart2(XlChartType:=xlColumnStacked, Left:=140, Top:=0, Width:=800, Height:=550, NewLayout:=True).Select
ActiveChart.SetSourceData Source:=Range(ThisWorkbook.Sheets("State").PivotTables("StateTableName").TableRange2.Address)
ActiveChart.ChartTitle.Text = "State Resourcing"

This is fine when you first create the chart, but if I need to change the format of an existing pivot chart, I need to replace "ActiveChart." with something like: ThisWorkbook.Sheets("State").ChartObjects("MyPivotChart").ChartTitle

How can I name a pivot chart and then later refer to that pivot chart by name?

Answer 1

To reference a chart by name, you firstly must give it a name... Try adding it during its creation:

Your code:

ThisWorkbook.Sheets("State").Shapes.AddChart2(XlChartType:=xlColumnStacked, Left:=140, Top:=0, Width:=800, Height:=550, NewLayout:=True).Select
ActiveChart.SetSourceData Source:=Range(ThisWorkbook.Sheets("State").PivotTables("StateTableName").TableRange2.Address)
ActiveChart.ChartTitle.Text = "State Resourcing"

just add:

ActiveChart.Parent.Name = "MyPivotChart1" 'Chart Object keeps the name...

Then you can obtain the object in this way:

Dim chO as ChartObject
Set chO = sh.ChartObjects("MyPivotChart1")
Debug.Print chO.Chart.ChartTitle.Text

Answer 2

I've found that my issue was trying to use the ChartObjects collection instead of the Chart object.

I was attempting to use this (which failed):

ThisWorkbook.Sheets("MySheet").ChartObjects("MyPivotChart").ChartTitle.Text = "My Title"

Where the correct code is:

ThisWorkbook.Sheets("MySheet").ChartObjects("MyPivotChart").Chart.ChartTitle.Text = "My Title"

Setting the .Name property as FaneDuru described is also required before this works. I had left this part out of my question as setting the name was working correctly for me, but it's important context for anyone searching for this answer in the future.