Adding a secondary category (x) axis to a chart with VBA

[MacGyver7640]

This should be a simple question, but I have been unable to figure it out. Would really appreciate some help!

The goal of this code is to find all of the charts in the workbook named "IndexChart" and changing their minimum values (and the maximum values for the horizontal axis).

I have had success with the values and hiding the axis titles if I manually add the secondary category axis...but it won't add the secondary axis if it's not already there.

Sub MinDateIndexCharts()
Dim ch As ChartObject
Dim ws As Worksheet

On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            If ch.Name = "IndexChart" Then
            ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)
            With ch.Chart
'                .Axes(xlCategory, xlPrimary).HasAxis = True
                .Axes(xlCategory, xlPrimary).MinimumScale = Range("ID").Value
                'Set up the secondary x-axis as the same as the primary x-axis
                'So the lines don't scramble when the XYScatter lines (vertical lines) look at the secondary axis instead
                .Axes(xlCategory, xlSecondary).MinimumScale = Range("ID").Value
                .Axes(xlCategory, xlSecondary).MaximumScale = Range("CD").Value
                'hides secondary axis
                .Axes(xlCategory, xlSecondary).MajorTickMark = xlNone
                .Axes(xlCategory, xlSecondary).TickLabelPosition = xlNone
            End With

            End If
        Next ch
    Next ws
End Sub

The recorder tells me to do "ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)" but that is apparently not working. Tried the .HasAxis too. Heck, tried both together, ha!

Thanks for your help! Finally narrowed down my problem (needing to add a secondary axis) now trying to figure out how to execute it!

 

[MacGyver7640]

Success! The proper code is

.HasAxis(xlCategory, xlSecondary) = True

the finished product (for others with the same problem)

Sub MinDateIndexCharts()
Dim ch As ChartObject
Dim ws As Worksheet

On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            If ch.Name = "IndexChart" Then

            With ch.Chart
                .Axes(xlCategory, xlPrimary).MinimumScale = Range("ID").Value
                'Set up the secondary x-axis as the same as the primary x-axis
                .HasAxis(xlCategory, xlSecondary) = True
                .Axes(xlCategory, xlSecondary).MinimumScale = Range("ID").Value
                .Axes(xlCategory, xlSecondary).MaximumScale = Range("CD").Value
                'hides secondary axis
                .Axes(xlCategory, xlSecondary).MajorTickMark = xlNone
                .Axes(xlCategory, xlSecondary).TickLabelPosition = xlNone
            End With

            End If
        Next ch
    Next ws
End Sub