How to Add or Edit Chart Title Using VBA

Typically, If you have titles in your data, Excel will automatically add a relevant title to the chart. But if no titles present in the data or if titles are not selected when inserting the chart, then the created chart will not have a title. And sometimes you might need to add a different title to the chart than present in the data. So in this lesson you will learn how to add or edit a chart title using VBA. Let’s consider this sample chart.

This chart was created from the data below.

If you look at the above Excel line chart, you will notice that the chart was created with the default title text called “Chart Title”. So in this lesson first let’s look at how to edit an existing title like that.

Assume you want to edit a title of an active chart. You can do it very easily as shown below.

Sub EditActiveChartTitle()

     With ActiveChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

However, for this macro to be worked, your chart should be in an active state(Selected). Otherwise the VBA program will throw an error like this.

But if you know the name of the chart then you can develop a VBA code to edit the title of the chart independent of whether it is active or not. For example, the subroutine below will change the chart title to “Monthly Sales” of a chart called “Chart 2”.

Sub EditTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Don’t know how to find the name of a chart? Check this post to learn how.
Find the name of a chart in Excel

Sometimes you might need to add a chart title to a chart which doesn’t have any title. Here is an example. This chart doesn’t have any title. As mentioned above this can happen if no titles present in the data or if the user doesn’t select the titles when inserting the chart. So let’s see how we can add a title to it.

To add a chart title, we need to do a very simple change to the above VBA code we used to edit the title. The only change needed is we should make the HasTitle property from False to True. So this is how you can add a chart title to an active chart by changing the HasTitle property.

Sub AddChartTitle()

     With ActiveChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Also If you know the name of the chart you want to add the title then you can change the above VBA code like this.

Sub AddTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Here is the result of the above macros.

Also Read
How to create a line chart automatically - Excel VBA
Swap Axis of an Excel Chart Without Changing Excel Sheet Data
Formatting Excel Line Charts Using VBA