runtime errors with excel recorded macro

Question

I want to create a chart that has 2 bars and a line (for a reference line) and I want to record a macro to do it (because I will need to do it for many different spreadsheets). I am very new at doing macros in excel- I mainly work in SAS. The error is in the first selection.MajorTickMark statement-I get a runtime error 438:

Sub Macro4()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue, xlSecondary).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 0.9
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 1
    Selection.MajorTickMark = xlNone
    Selection.TickLabelPosition = xlNone
End Sub

score 0 · Answer 1 · edited May 23 '17 at 12:10

Try this one:

Sub Macro4()
    With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue, xlSecondary)
        'As I see it should be MinimumScale = 0.9 (not MaximumScale)
        .MinimumScale = 0.9
        .MaximumScale = 1
        .MajorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
End Sub

Btw, it would be very helpful for you to read article: How to avoid using select/active statements.

runtime errors with excel recorded macro

1 Answers1