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