Assign charts names in VBA?

[PaulW]

Two charts, one on "Options" page, one on "Data" page. Make a simple macro
that switches the two round.
Recorded a macro, and when I tried to run it I got an error.


ActiveSheet.ChartObjects("Chart 51").Activate
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"

Then Chart 24 to "Options". Also recorded the bit where they switch back.
But Chart 24 is now Chart 25, and Chart 51 is now Chart 54. The numbers seem
to go up each time something is done with a chart... As such, when I try and
run the macro again the charts have different numbers and it fails.

Can you assign a name to a chart to provent this?

 

[Don Guillett]

the macro recorder is your friend
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/10/2006 by Don Guillett
'

'
ActiveSheet.Shapes("Chart 2").Select
Selection.Name = "namedchart"
End Sub
shorten to
sheets("sheet2").Shapes("Chart 2").Name = "namedchart"

 

[PaulW]

the macro recorder is your friend

Cheers
Copied that macro into Macro1, found the number of the chart by putting it
in a window, then changed the macro to this number. This allowed me to name
both charts as I wanted, solving the problem.

I get the feeling this roundabout route wasn't what you had in mind when you
answered, but if it works, it works

 

[Don Guillett]

It wouldn't be round about IF you already knew the chart names. It could be
done from anywhere in the workbook. If there is only one chart per page you
probably don't need the name.

 

[Jon Peltier]

You can also use

activechart.parent.name = "namedchart"

If he charts are created in code, change the name of the chart object when
the chart is being created.

Manually, select a chart using Shift+click. It should have white handles
instead of black, which means you've selected the chart object, a shape
object that serves as a container for an embedded chart. In the Name Box
(top left, next to the formula bar, just below the menus), you'll see a name
like "Chart 55". Change this to whatever name you want and press enter.

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