XL2002 - Setting same MaxValue on 2 axes...

[Trevor Williams]

I have a chart with 2 axes (one for columns, one for lines). I need to set
the Maximum values on both axes to be the same, BUT if at all possible, I'd
like Excel to automatically choose the Maximum Value of either axis and then
apply that value to the other axis. i.e. If the primary axis is automatically
set at 100 and the secondary axis is automatically set at 500, I need to
reset the primary axis to 500.

Thanks in Advance

Trevor Williams

 

[Peter T]

Hi Trevor,

One way -

Sub test()
Dim axs As Axes
Dim mx1, mx2
Set axs = ActiveSheet.ChartObjects(1).Chart.Axes ' Change to relevant chart

mx1 = axs(xlValue, xlPrimary).MaximumScale
mx2 = axs(xlValue, xlSecondary).MaximumScale

If mx1 > mx2 Then
axs(xlValue, xlSecondary).MaximumScale = mx1
ElseIf mx2 > mx1 Then
axs(xlValue, xlPrimary).MaximumScale = mx2
End If

End Sub

Or maybe monitor the maximum point value in all series.

You might also want to recheck in a change event or, if the data is returned
by cell formulas the calculate event (events of the sheet containing the
data). Include an error handler.

Regards,
Peter T

 

[Trevor Williams]

Hi Peter - Thanks very much for this, it works a treat!

Trevor