Label Chart Title & Axes

Hello,

there are several problems with your code.

You need to properly reference the chart and its elements. The first With block is not referencing the correct object. Also, there is a "1" instead of an "l" in "With ActiveChart.Axes(x1Category, 2)".

Details matter. You need to be aware of what you type. Excel will not tolerate typos. If you can't trust your typing skills, use Option Explicit above your code. Then these typing mistakes will be picked up by the compiler before it even tries to run the code.

Without context it is hard to figure out what you want to do, but you can set the chart title and the primary and secondary X axis titles with this code:

Option Explicit

Sub myMacro()
Dim myChart As Chart

Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart

With myChart
.HasTitle = True
.ChartTitle.Text = "Total Productivity"
End With

With myChart.Axes(xlCategory, 1)
.HasTitle = True
.AxisTitle.Text = "Productivity Levels"
End With


With myChart.Axes(xlCategory, 2)
.HasTitle = True
.AxisTitle.Text = "Change in employment share, percentage points"
End With
End Sub

At least that runs on a chart with data on the primary and secondary axis in my tests. If it is not what you need, please post a sample file that represents your data structure and chart. Replace confidential data with dummy data.

cheers, teylyn

Do you want the macro to run on an individual sheet or on all charts in the workbook?

Are all your charts on chart sheets? Or do you have charts in normal worksheets? The code will differ.

(it's rather late at night where I live, so I will be off-line for a few hours. Other folks may be able to help in the mean time)