Create chart title from sheetname

Question

I am hoping to amend my charts using VBA

I have created a Named Range using formula, named it SheetNames

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then in VBA, I was trying to change the chart title:

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Range("SheetNames").Value

But unsuccessful. I tried using a formula to call teh sheet name in cell B1 and it works

=SheetNames

then in VBA using the follow and work well:

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Range("B1")

What could I do to make the first bit works?

You might want to be more specific with regards to why you cannot change the chart title. Is there an error message? — jsantander

Gary's Student Gary's Student · Accepted Answer · 2014-04-01T12:05:43

Why use a formula to get the name of the sheet:

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = ActiveSheet.Name