Excel VBA - 图表格式化轴标签

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

2 个解决方案

#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

我想这是因为你还没有创建辅助轴。格式化所有系列后,请尝试放置这两行

#2

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