Naming a chart object

[Jeff M]

This has got to be simple but I can't find it...
I'm using vba to create several charts as objects (as opposed to on their
own page). I want to refer to them later, so I want to give them meaningful
names - how do I name them something other than the default "Chart 1", "Chart
2" etc?

Thanks!

 

[John Mansfield]

Jeff,

To get the current name of the chart, activate it by clicking on it once and
run this macro:

Sub GetName()
MsgBox "The chart name is: " & ActiveChart.Parent.Name
End Sub

To rename the chart, activate it and run this macro (substitute Chart2 with
the name of your choice):

Sub RenameChart()
ActiveChart.Parent.Name = "Chart2"
End Sub

 

[Tushar Mehta]

First of all, for an embedded chart you are better off naming the
parent container, i.e., the chartobject, not the chart itself.

Second, it depends on how you create them. If you modifed the code
from the macro recorder, you would use something like:
Sub Macro2()
Dim x As Chart
Set x = Charts.Add()
With x
.SetSourceData Source:=Sheets("Sheet1").Range("A1:A12")
.ChartType = xlColumnClustered
Set x = .Location(Where:=xlLocationAsObject, Name:="Sheet1")
End With
x.Parent.Name = "My Chart"
MsgBox ActiveSheet.ChartObjects("my chart").Chart.Name
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Jeff -

I have a web page that describes the whole chart naming process:

http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jeff M]

Thanks to all 3 of you for your help - all three posts were accurate and
helpful, though they each approached the problem from a different angle.

Thanks -

 

[Tom]

Jon,
When I use the following code:

ActiveChart.Parent.Name = "z"


the VB editor automatically adds the autosave code:

Application.Goto Reference:="PERSONAL.XLS!renamechart"
Windows("PERSONAL.XLS").Activate
ActiveWindow.WindowState = xlNormal

How can I get it not to save each time I rename a chart

 

[Jon Peltier]

I have no idea where that other code comes from. It doesn't look like any
autosave routine to me.

- Jon