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