Review https://myengineeringworld.net/2015/...-axis-vba.html
I have done this with primary Y-axis, not secondary. Also, all charts I have done this with are XYScatter type. Example:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'format graphs
Dim MinUWT As Double, MaxUWT As Double
Dim MinDry As Double, MaxDry As Double
Dim MinSoak As Double, MaxSoak As Double
Dim MinRat As Double, MaxRat As Double
With Me
If Not IsNull(!MinOfA) Then
MinUWT = Int(!MinOfU) - IIf(!MaxOfU - !MinOfU <= 2, 3, 1)
MaxUWT = MinUWT + 8
MinDry = Int(!MinOfD / 5) * 5 - (40 - Int((Int(!MaxOfD / 5) * 5 + 5 - Int(!MinOfD / 5) * 5) / 10) * 10) / 2
MaxDry = MinDry + 40
MinSoak = Int(!MinOfS / 5) * 5 - (40 - Int((Int(!MaxOfS / 5) * 5 + 5 - Int(!MinOfS / 5) * 5) / 10) * 10) / 2
MaxSoak = MinSoak + 40
MinRat = Int(!MinOfR / 5) * 5 - (40 - Int((Int(!MaxOfR / 5) * 5 + 5 - Int(!MinOfR / 5) * 5) / 10) * 10) / 2
MaxRat = MinRat + 40
.gphWeight.Axes(xlValue).MinimumScale = MinUWT
.gphWeight.Axes(xlValue).MaximumScale = MaxUWT
.gphITSdry.Axes(xlValue).MinimumScale = MinDry
.gphITSdry.Axes(xlValue).MaximumScale = MaxDry
.gphITSsoak.Axes(xlValue).MinimumScale = MinSoak
.gphITSsoak.Axes(xlValue).MaximumScale = MaxSoak
.gphITSret.Axes(xlValue).MinimumScale = MinRat
.gphITSret.Axes(xlValue).MaximumScale = MaxRat
If Me!Metric = True Then
.gphWeight.Axes(xlValue, xlPrimary).AxisTitle.Text = "Unit Weight, kg/cu.cm"
.gphGradation.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Sieve Size (mm)"
.gphITSdry.Axes(xlValue, xlPrimary).AxisTitle.Text = "ITS Dry, kg/cu.cm"
.gphITSsoak.Axes(xlValue, xlPrimary).AxisTitle.Text = "ITS Soaked, kg/cu.cm"
End If
End If
End With
End Sub
xlValue is Y-axis, xlCategory is X-axis