Labeling a Y-axis w VBA

[Joe A]

I am referencing "Excel 2016: VBA and Macros."

I tried the code at the bottom of page 311 to create a chart, add a chart and the code on page 316 to add and format a chart title and label the X-axis (months).

I have unsuccessfully tried to use similar code to label the vertical Y-Axis.

Here is the code (not including my failed attempt)

Sub CreateChartUsingAddchart2()
Dim CH As Chart
Range("a3:g6").Select
Set CH = ActiveSheet.Shapes.AddChart2( _
Style:=201, _
XlChartType:=xlColumnClustered, _
Left:=Range("b8").Left, _
Top:=Range("b8").Top, _
Width:=Range("b8:g20").Width, _
Height:=Range("b8:g20").Height, _
newlayout:=True).Chart
CH.ChartTitle.Caption = "Sales by Region"

CH.SetElement msoElementPrimaryCategoryAxisTitleHorizontal
CH.Axes(xlCategory, xlPrimary).AxisTitle.Caption = "Months"
CH.Axes(xlCategory, xlPrimary).AxisTitle. _
Format.TextFrame2.TextRange.Font.Fill. _
ForeColor.ObjectThemeColor = msoThemeColorAccent2

With CH.ChartTitle.Format.TextFrame2.TextRange.Font
.Name = "Arial"
.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.Size = 14
End With
End Sub

Can someone help me understand how to refer to and label the Y-axis using similar code? Or point me to a good place where I might find that information.

Thanks.

 

[Domenic]

For the Y-axis, try...

CH.SetElement msoElementPrimaryValueAxisTitleVertical 'change the orientation as desired
CH.Axes(xlValue, xlPrimary).AxisTitle.Caption = "MyCaption" 'change the caption accordingly
CH.Axes(xlValue, xlPrimary).AxisTitle. _
    Format.TextFrame2.TextRange.Font.Fill. _
    ForeColor.ObjectThemeColor = msoThemeColorAccent2

Or, using the With/End With statement...

With CH
    .SetElement msoElementPrimaryValueAxisTitleVertical
    .Axes(xlValue, xlPrimary).AxisTitle.Caption = "MyCaption"
    .Axes(xlValue, xlPrimary).AxisTitle. _
        Format.TextFrame2.TextRange.Font.Fill. _
        ForeColor.ObjectThemeColor = msoThemeColorAccent2
End With

Hope this helps!

 

[Joe A]

Thanks for both sections of code. I found both solutions enlightening.
Joe A

 

[Domenic]

That's great, glad I could help, cheers!

 

[Jon Peltier]

Ugh, SetElement sucks. It just feels dirty.

With CH
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        With .Axes(xlValue, xlPrimary).AxisTitle
            .Caption = "MyCaption"
            .Format.TextFrame2.TextRange.Font.Fill. _
                ForeColor.ObjectThemeColor = msoThemeColorAccent2
    End With
End With