Excel VBA - Chart Formatting axis labels

Question

I am trying to add axes labels to my chart. Below is a part of my code.

First, my code is not producing an error when it runs the line which adds the primary axis title. Despite not registering an error, my chart still lacks a text box for an axis title.

Second, I am not sure why my code is generating an error when I try to add the secondary axis title. I copied this section of code from the macro recorder, so I am fairly certain that the syntax is correct.

Thanks for all your help!

Dim myChart As Chart, cht As ChartObject
Dim rngChart As Range, destSht As String
Dim rngData As Range

destSht = ActiveSheet.Name
Set myChart = Charts.Add
Set myChart = myChart.Location(where:=xlLocationAsObject, Name:=destSht)

With ActiveSheet.ListObjects("Table1").DataBodyRange
Set rngData = Union(.Columns(2), .Columns(9), .Columns(10), .Columns(11), .Columns(12))
End With

With myChart
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.ChartType = xlColumnClustered
.ChartStyle = 209
.FullSeriesCollection(1).Name = "AHT"
.SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = "AHT/Transfer Metrics"

'*****PROBLEM SECTION*****
.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
'*****Below is the line of code that is giving me an error*****
.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)
'******              *****

With .FullSeriesCollection(2)
    .Name = "Target AHT"
    .ChartType = xlLine
    .AxisGroup = 1
    .Select
        With Selection.Format.Line
            .Weight = 1
        End With
End With
With .FullSeriesCollection(3)
    .ChartType = xlLineMarkers
    .AxisGroup = 2
    .Name = "Transfers"
    .Select
        With Selection.Format.Line
            .Weight = 1.75
        End With
End With
With .FullSeriesCollection(4)
    .ChartType = xlLine
    .AxisGroup = 2
    .Name = "Target Transfers"
    .Select
        With Selection.Format.Line
            .Weight = 1
        End With
End With
End With

Andy · Accepted Answer · 2016-06-08 20:38:03Z

3

    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .AxisTitle.Text = "AHT (s)"
    End With

answered Jun 8, 2016 at 20:38

Andy

7381 gold badge17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JerryT · Accepted Answer · 2016-06-08 20:14:13Z

1

I think this is because you have not created the secondary axis yet. Try putting those 2 lines after you format all of your series

answered Jun 8, 2016 at 20:14

JerryT

1281 silver badge10 bronze badges

1 Comment

Andy Over a year ago

That did solve my second stated problem. However, It is still not populating a primary axis title box. I'm not getting an error for this, it seem to just be ignoring my code.

Collectives™ on Stack Overflow

Excel VBA - Chart Formatting axis labels

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related