[SOLVED] Excel VBA Macro Axis Font Formatting

Excel VBA Macro Axis Font Formatting

How can I tell the Axis to have a certain Font?

This is what I've got so far. The last five lines are a recorded macro, doesn't work. Excel highlights the line "Selection.Format.TextFrame2.TextRange.Font" yellow.

    With objChart.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlColumnClustered
        .HasLegend = False
        .HasTitle = False
        .SetElement (msoElementChartTitleNone)
        .SetElement (msoElementPrimaryValueAxisNone)
        .SetElement (msoElementPrimaryValueGridLinesNone)
        .HasAxis(xlCategory) = True
'            With .Axis(xlCategory).Select
'                Selection.Format.TextFrame2.TextRange.Font
'                    .BaselineOffset = 0
'                    .Name = "Arial"
'            End With

This is the recorded Macro:

Sub Macro_1()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    With ActiveChart.Axes(xlCategory).Select
        With Selection.Format.TextFrame2.TextRange.Font
            .BaselineOffset = 0
            .Name = "Times"
            .Size = 12
        End With
    End With
    
End Sub

Solution

If you put a break on the last line of code below and put a watch on ax you can run the code then examine the various properties of ax in the Watches pane. That's how I found ax.TickLabels.Font.Name...

Sub Macro_1()
    Dim cht As Chart, ax As Axis
    
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set ax = cht.Axes(xlCategory)
    ax.TickLabels.Font.Name = "Times"
End Sub

In your posted code

With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlColumnClustered
    .HasLegend = False
    .HasTitle = False
    .SetElement (msoElementChartTitleNone)
    .SetElement (msoElementPrimaryValueAxisNone)
    .SetElement (msoElementPrimaryValueGridLinesNone)
    .HasAxis(xlCategory) = True
    .Axes(xlCategory).TickLabels.Font.Name = "Times" '<<###