Secondary axis with the same number of steps as the primary axis?

Question

I am looking for a method to have the primary and secondary axes use the same number of steps (so that axis lines are always aligned on both axes).

Currently, my primary axis has 5 steps and my secondary has 6 steps. I want both axes to have the same number of steps.

Is this possible?

The chartdata varies on the selection that is made in a validation list. The proces for this:

On the dashboard, the user selects 5 options:

retailer
product
fact 1
fact 2
fact 3

fact 1 and fact 2 are on the primary axis; fact 3 is on the secondary axis.

The values which correspond to the choices made under 1 - 5 vary a lot. So the values for the primary and secondary axes change a lot (at every new selection).

For every new selection, code should calculate the new minimum and maximum values and the major unit for both axes in a way that the number of major grid lines is the same for both axes.

I guess the above should be triggered by a worksheet_change event. But then I would wonder how I could set the major unit for both the primary and secondary axes.

EDIT:

I just found this site with vba code which should do what I am trying to do. I have not tried it yet, but I will.

http://www.myengineeringworld.net/2015/04/adjust-secondary-y-axis-scale-chart-vba.html

Luboš Suk Luboš Suk · Accepted Answer · 2016-05-10T12:08:54

Yes it is, just assign same values to both primary and secondary axis

edit: so you just want someone to write code for you? My answer was exactly to your question: whether it's possible... Code below will align both secondary and primary axes min and max, so probably the majorUnit will be the same. If not, its not so hard to update the macro to do this. If it's so impossible please let me know

Sub alignAxis()

    Dim mySheet As Worksheet
    Set mySheet = Sheets("Sheet3")

    Dim primaryMax As Integer
    Dim primaryMin As Integer
    Dim secondaryMax As Integer
    Dim secondaryMin As Integer
    Dim max As Integer
    Dim min As Integer

    With mySheet
        With .ChartObjects("myChart").Chart.Axes(xlValue)
            primaryMin = .MinimumScale
            primaryMax = .MaximumScale
        End With
        With .ChartObjects("myChart").Chart.Axes(xlValue, xlSecondary)
            secondaryMin = .MinimumScale
            secondaryMax = .MaximumScale
        End With

        If primaryMax > secondaryMax Then
            max = primaryMax
        Else
            max = secondaryMax
        End If

        If primaryMin < secondaryMin Then
            min = primaryMin
        Else
            min = secondaryMin
        End If
        With .ChartObjects("myChart").Chart.Axes(xlValue)
            primaryMin = min
            primaryMax = max
        End With
        With .ChartObjects("myChart").Chart.Axes(xlValue, xlSecondary)
            secondaryMin = min
            secondaryMax = max
        End With

    End With

End Sub

Secondary axis with the same number of steps as the primary axis?

1 Answers