Changing Secondary Axis Chart Formatting

[mtdewrocks]

Let me preface this with I am a very new VBA user--with that said, can someone help me figure out how to change my secondary axis formatting? My boss has very particular likes and I am trying to slowly set up a macro that can run and update all formatting to meet his preferences. In this example I changed the primary axis to format as a number with 2 decimal places, and am trying to change the formatting of my secondary axis to change the numbers from something like 140000 to 14 on a chart that is not currently active. I recorded the steps with a macro and then tried to get it to run and I get an error.

Sub selectotherchart()
    Sheets("2-Year Moving Average").Select
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "#,##0.00"
    ActiveChart.Axes(xlValue, xlSecondary).Select
    ActiveChart.Axes().DisplayUnit = xlThousands----------------->get error saying "Object doesn't support this property or method    ActiveChart.Axes(xlValue, xlSecondary).HasDisplayUnitLabel = False
End Sub

 

[Domenic]

Try...

Sub selectotherchart()
    With Sheets("2-Year Moving Average")
        With .ChartObjects("Chart 1").Chart
            .Axes(xlValue).TickLabels.NumberFormat = "#,##0.00"
            .Axes(xlValue, xlSecondary).DisplayUnit = xlThousands
        End With
        .Activate
    End With
End Sub

Hope this helps!