I am a new VBA programmer so please forgive this simple question.

I am trying to change the Title of a chart to the text in a cell on a
worksheet. This code successfully sets the title of the chart to "Stupidity":

Private Sub Chart_Activate()
Dim strChartName As String
strChartName = "stupidity"
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

I would like to replace "Stupidity" with reference to cell Sheet1!$N$1 but
cannot seem to get it to work without running into syntax errors I do not
understand.

Any help gratefully appreciated.

This seems to do it:

Public Sub Chart_Activate()
Dim strChartName As String
strChartName = Worksheets(1).Range("N1").value
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

Thanks.

"Mike M 91107" wrote:

I am a new VBA programmer so please forgive this simple question.

I am trying to change the Title of a chart to the text in a cell on a
worksheet. This code successfully sets the title of the chart to "Stupidity":

Private Sub Chart_Activate()
Dim strChartName As String
strChartName = "stupidity"
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

I would like to replace "Stupidity" with reference to cell Sheet1!$N$1 but
cannot seem to get it to work without running into syntax errors I do not
understand.

Any help gratefully appreciated.

Hi,

Of course you can do this by selecting the title and then clicking on the
Formula bar and typing = and then clicking on a cell in the spreadsheet.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike M 91107" wrote:

This seems to do it:

Public Sub Chart_Activate()
Dim strChartName As String
strChartName = Worksheets(1).Range("N1").value
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

Thanks.

"Mike M 91107" wrote:

I am a new VBA programmer so please forgive this simple question.

I am trying to change the Title of a chart to the text in a cell on a
worksheet. This code successfully sets the title of the chart to "Stupidity":

Private Sub Chart_Activate()
Dim strChartName As String
strChartName = "stupidity"
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

I would like to replace "Stupidity" with reference to cell Sheet1!$N$1 but
cannot seem to get it to work without running into syntax errors I do not
understand.

Any help gratefully appreciated.

Hi,

To create a link between cell and title text use,


Chart1.ChartTitle.Text = "='" & worksheets(1).name & "'!" & _
Worksheets(1).Range("N1").address(,,xlr1c1)

Cheers
Andy

Mike M 91107 wrote:
This seems to do it:

Public Sub Chart_Activate()
Dim strChartName As String
strChartName = Worksheets(1).Range("N1").value
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

Thanks.

"Mike M 91107" wrote:


I am a new VBA programmer so please forgive this simple question.

I am trying to change the Title of a chart to the text in a cell on a
worksheet. This code successfully sets the title of the chart to "Stupidity":

Private Sub Chart_Activate()
Dim strChartName As String
strChartName = "stupidity"
Chart1.HasTitle = True
Chart1.ChartTitle.Text = strChartName

End Sub

I would like to replace "Stupidity" with reference to cell Sheet1!$N$1 but
cannot seem to get it to work without running into syntax errors I do not
understand.

Any help gratefully appreciated.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info