Renaming the active chart

[MWE]

I am trying to rename the active chart using VBA. I know I have done this before, but the obvious methods do not seem to work. I wrote a little test procedure[VBA]Sub ActiveChartRename()

MsgBox "renaming chart " & ActiveChart.Name
On Error Resume Next
ActiveChart.Name = "TestName"
MsgBox ActiveChart.Name & vbCrLf & Err.Number & vbTab & Err.Description

End Sub
[/VBA]It fails with a 1004 error

[TonyJollans]

Are you trying to rename a chart sheet (which should work) or a chart object in another sheet (which won't - but it's a bit funny and I don't remember all the details)?

[MWE]

Are you trying to rename a chart sheet (which should work) or a chart object in another sheet (which won't - but it's a bit funny and I don't remember all the details)?

I am trying to rename an embedded chart.

I did some additional testing and poked around the web looking for some clues. Jon Peltier's site has some useful information on renaming charts, chartobjects, etc. However, I found some problems due to the inconsistent linkage between the active chart and its chartobject parent:

As per Jon's site, you cannot rename a chart, particularly the ActiveChart, directly. Rather you have to rename the relevant ChartObject. If you use ActiveChart.Name to get the name of the active chart, the name fetched is a combination of the ActiveSheet name and the ActiveChart name: = ActiveSheet name & ? ? & ActiveChart name. Thus for a chart named ?Chart23? on Sheet ?Sheet1?, ActiveChart.Name = ?Sheet1 Chart23?

So to find the name of the ActiveChart and use that to rename the relevant chartobject:[VBA]
Dim OldName as String
Dim NewName as String

OldName = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name) - 1)
ActiveSheet.ChartObject(OldName) = NewName[/VBA]



But, Excel seems to get confused when you rename a chart this way. In particular, the connection between the chart and the chartobject appears to be broken until the file is saved. For example, assume a starting condition with the target chart called ?ABC?. if you rename the chart using the above code to ?DEF? and then display the relevant names:

ActiveChart.Name is still ?ABC?
ActiveSheet.ChartObjects(?DEF?) = ?DEF?
ActiveSheet.ChartObjects(?ABC?) no long exists

If you run the rename code again, ActiveChart.Name is still ?ABC? and using OldName to rename the relevant CharObject will not work because ActiveSheet.ChartObjects(?ABC?) does not exist.

But if you save the file after rename the ActiveChart, the connection between the chart and its chartobject is reestablished and ActiveChart.Name is now ?DEF?

Seems pretty strange !

[Andy Pope]

Hi,

For embedded chart objects you need to use the following[vba]Sub ActiveChartRename()

MsgBox "renaming chart " & ActiveChart.Parent.Name
On Error Resume Next
ActiveChart.Parent.Name = "TestName"
MsgBox ActiveChart.Parent.Name & vbCrLf & Err.Number & vbTab & Err.Description

End Sub[/vba]

[MWE]

Hi,

For embedded chart objects you need to use the following[vba]Sub ActiveChartRename()

MsgBox "renaming chart " & ActiveChart.Parent.Name
On Error Resume Next
ActiveChart.Parent.Name = "TestName"
MsgBox ActiveChart.Parent.Name & vbCrLf & Err.Number & vbTab & Err.Description

End Sub[/vba]

Andy: Thanks. Your much simplified code does rename the parent much more efficiently, but it does not solve the subsequent problem of the ActiveChart's name not changing until the file is saved. This is not a major problem but can be confusing. Perhaps the rule is to never refer to the target chart by its name but to always use the parent or chartobject name.

Do you know why this inconsistency occurs?

[Andy Pope]

I was not aware of that. I always use the parent.name property for embedded charts.