Chart Macro

[James]

Is there a way to record a macro when creating a chart and use the value of
A1 as the chart title and the value of A2 as the name of the chart.
Right now, when I look in the VBA editor the macro has recorded the name as
"chart 11" or something like that.
I've tried to use ActiveChart.Name = "James" as the name of the chart, but I
keep getting an error.
Can anyone please help me?
Thanks James
--

[John Mansfield]

James,

Add this code to a standard module. Select the chart and then run the macro
(note: assumes you are referring to an embedded chart on Sheet1 of your
workbook).

Sub ChartMacro()
Dim Cht As Chart
Set Cht = ActiveChart
Cht.Parent.Name = Sheets("Sheet1").Range("A1").Value
Cht.ChartArea.Select
With Cht
.HasTitle = True
.ChartTitle.Characters.Text = Sheets("Sheet1").Range("A2").Value
End With
MsgBox "The chart name is: " & Cht.Parent.Name
MsgBox "The chart title is: " & Cht.ChartTitle.Characters.Text
End Sub

----
Regards,
John Mansfield
http://www.pdbook.com

"James" wrote:

> Is there a way to record a macro when creating a chart and use the value of
> A1 as the chart title and the value of A2 as the name of the chart.
> Right now, when I look in the VBA editor the macro has recorded the name as
> "chart 11" or something like that.
> I've tried to use ActiveChart.Name = "James" as the name of the chart, but I
> keep getting an error.
> Can anyone please help me?
> Thanks James
> --
>

[James]

John,
Thanks, you make it look so easy ;-)
James

"John Mansfield" wrote:

> James,
>
> Add this code to a standard module. Select the chart and then run the macro
> (note: assumes you are referring to an embedded chart on Sheet1 of your
> workbook).
>
> Sub ChartMacro()
> Dim Cht As Chart
> Set Cht = ActiveChart
> Cht.Parent.Name = Sheets("Sheet1").Range("A1").Value
> Cht.ChartArea.Select
> With Cht
> .HasTitle = True
> .ChartTitle.Characters.Text = Sheets("Sheet1").Range("A2").Value
> End With
> MsgBox "The chart name is: " & Cht.Parent.Name
> MsgBox "The chart title is: " & Cht.ChartTitle.Characters.Text
> End Sub
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
> "James" wrote:
>
> > Is there a way to record a macro when creating a chart and use the value of
> > A1 as the chart title and the value of A2 as the name of the chart.
> > Right now, when I look in the VBA editor the macro has recorded the name as
> > "chart 11" or something like that.
> > I've tried to use ActiveChart.Name = "James" as the name of the chart, but I
> > keep getting an error.
> > Can anyone please help me?
> > Thanks James
> > --
> >