Naming a Chart for use in a Macro

[MarkNance]

I am trying to create a macro that uses several charts that need to have a unique name instead of the chart(3) format. I tried using the help function in Visual Basic but it was not much help.

Thanks

[Damon Ostrander]

Hi Mark,

Naming charts is easy, but there is a trick to it: you have to select the chart rather than the chart area. If you left-click on a chart the Chart Area becomes selected. If you Ctrl-left-click, the chart itself becomes selected. Once selected, you can name the chart the same way you would name a cell range--by simply entering the name into the Name box just above cell A1. You can thenceforth use this name to refer to the chart in the ChartObjects collection from VBA.

[MarkNance]

That is great, thanks. Is there a way of doing that in Visual Basic so I can include it in a Macro?

[MarkNance]

I guess what I mean is there a way to name the chart within a macro?

Mark

[Damon Ostrander]

Hi again Mark,

Since you said "name" a chart rather than "rename" a chart, I assume you are creating the chart in VBA and want to name it as you create it. Here is a simple example of naming a chart during creation in VBA:

Sub MakeChart()

' Macro to demonstrate creation and naming of embedded chart
' author: Damon Ostrander
' mailto: VBAexpert@piadamon.com
' Date: 5/9/2001

Dim MyChart As Object
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B21"), PlotBy _
:=xlColumns
Set MyChart = ActiveChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With MyChart
.Parent.Name = "DamonsChart"
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Damon