I simply want to add a chart title to my chart using vba. I actually want to do it recursively for every chart in every sheet, but I can't even get 1 chart to work. Here is the code I have:
Dim chnam
chnam = Left(ActiveSheet.Name, (Len(ActiveSheet.Name) - 9))
With ActiveWorkbook.ActiveSheet.ActiveChart
.HasTitle = True
.ChartTitle = chnam
End With
Here is my chart:
When I run my code, I get:
Object does not support this property or method
6 Answers
Try this:
Dim chnam as string
chnam = Left(ActiveSheet.Name, (Len(ActiveSheet.Name) - 9))
With ActiveWorkbook.ActiveSheet.ActiveChart
.HasTitle = True
.ChartTitle.Select
.ChartTitle.Text = chnam
End With
Try changing the code to this:
Dim chnam As String
chnam = Left(ActiveSheet.Name, (Len(ActiveSheet.Name) - 9))
With ActiveWorkbook.ActiveChart
.HasTitle = True
.ChartTitle.Select
.ChartTitle.Text = chnam
End With
...which will work for the active chart, then add a For Each... if you want to apply to all charts in all sheets in the activeworkbook.
I've had the same problem and can't find the answer - but have found something that works. What I mean by that is I don't know why it works but it does.
Try this - worked for me.:
With ActiveWorkbook.ActiveChart
.HasTitle = False // added line - not sure why it works!
.HasTitle = True
.ChartTitle.Text = "Chart Title"
End With
Hope that helps.
The above failed for me in Office 2016. I had to use the Worksheet.Shapes object.
Debug.Assert ActiveWorkbook.Charts.Count = 0 ' Strange, but true
ActiveSheet.Shapes(1).Chart.ChartTitle.Text = chnam
The following subroutine works for me.
' Set title of chart with given name on given worksheet
Private Sub RetitleChart(sheetExport As Worksheet, strChartName As String, strChartTitle As String)
Dim chartOverview As Chart
Set chartOverview = sheetExport.Shapes(strChartName).Chart
chartOverview.ChartTitle.Text = strChartTitle
Set chartOverview = Nothing
End Sub
Another method to set a Chart's Title text is to use the ChartWizard method, thus:
Dim chnam as string
chnam = Left(ActiveSheet.Name, (Len(ActiveSheet.Name) - 9))
ActiveWorkbook.ActiveSheet.ActiveChart.ChartWizard Title:=chnam
It's worthwhile familiarizing yourself with the documentation for this method:
and that of Chart itself:
(Which has a link to the documentation for the ChartTitle object.)
I had the same problem - on my current machine, Excel 2017, while I hadn't had it 6 months ago; having code
pchrTheChart.HasTitle = True
pchrTheChart.ChartTitle.Select
would err on the second line; if I inserted a breakpoint and then resumed with no change, it worked fine.
But, variant on a solution above worked; even though the chart was just created and started out without a title, I have to explicitly turn the title off before turning it on and now it works with no issues every time.
pchrTheChart.HasTitle = False
pchrTheChart.HasTitle = True
pchrTheChart.ChartTitle.Select
Original poster said they didn't know why it worked; answer, to me it seems we're working around a VBA bug, I imagine something to do with null handling.
