Chart Title from Cell in Sheet1?

[Mike M 91107]

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.

 

[Mike M 91107]

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.

 

[Shane Devenshire]

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.

 

[Andy Pope]

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