Inserting chart title within a macro

[paul061]

I am creating a chart using a macro. I would like to automatically put the chart title in when the macro is run. The data for the chart title changes each time. I thought I could just use the copy/paste while recording the macro but I cannot make data paste in the chart. Is there a way to call out the name using code within the macro. For instance, the title of the chart would be "sales 2007" and it's located in cell B7, how would I tell the macro to put the contents of cell B7 into the chart?

Thanks!

 

[Trevor G]

Check this out

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mcrChartTitle()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' mcrChartTitle Macro</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br>    ActiveSheet.ChartObjects("Chart 1").Activate<br>    ActiveChart.ChartTitle.Text = Range("B7")<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[VoG]

Or

With ActiveSheet.ChartObjects(1).Chart
    .HasTitle = True
    .ChartTitle.Text = Range("B7").Value
End With

 

[paul061]

Awesome!!!! Thank you

 

[paul061]

OK one more, I'm trying to use a cell to name a series. I tried to emulate what was done previously but with zero programming training I'm shooting in the dark. This is what I tried.

harts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet9").Range("H19:L24"), PlotBy _
:=xlRows
ActiveChart.SeriesCollection(1).Text = Range("A9").Value
ActiveChart.SeriesCollection(4).Name = "=""Spec Min"""
ActiveChart.SeriesCollection(5).Name = "=""Spec Max"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet9"
With ActiveChart
.HasTitle = True
.ChartTitle.Text = Range("B4").Value
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Location"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Interference"
End With
ActiveChart.SeriesCollection(5).Select


Thanks for your patience.