Maximum scale of secondary horizontal X-axis VBA

1
votes

By the following code Max. and Min. scale for primary horizontal X-axis of a chart in vba is being set;

Sub chart_set()
    Dim objCht As ChartObject
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
    .MinimumScale = 0
    .MaximumScale = my_max_var
    End With
End Sub

is there any way to set the secondary one's as well?

1
vba

1 Answers

2
votes

First, the code below will Set objCht to the ChartObject in ActiveSheet (see here recommendation how to stay away from ActiveSheet How to avoid using Select in Excel VBA macros)

The code below will check if a secondary axis is applied, if not it will add it to the chart. Afterwards, it will modify the MinimumScale and MaximumScale to 20 and 50 (just random numbers I've selected).

Code

Option Explicit

Sub chart_set()

Dim objCht  As ChartObject
Dim my_max_var As Long

' just for my tests
my_max_var = 1000

' set chart object
Set objCht = ActiveSheet.ChartObjects(1)

With objCht
    With .Chart.Axes(xlValue, xlPrimary)
        .MinimumScale = 0
        .MaximumScale = my_max_var
    End With

    ' check if Secondary Axis is applied
    If .Chart.HasAxis(xlValue, xlSecondary) = False Then
        .Chart.HasAxis(xlValue, xlSecondary) = True
    End If

    ' modify minimum and maximum values of seconday axis
    With .Chart.Axes(xlValue, xlSecondary)
        .MinimumScale = 20
        .CrossesAt = 20
        .MaximumScale = 50
    End With

End With

End Sub