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, Apr 01 '14 at 12:01

score 5 · Answer 1 · answered Apr 01 '14 at 12:05

5

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

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

answered Apr 01 '14 at 12:05

Gary's Student

1 Answers1