VBA to auto adjust the scale of Y axes of 2 charts based on the maximum value among both of the charts

[spgexcel]

Hi

I have been trying to zero down to a vba code for a task.
I have 2 charts in excel and need to autoadjust the scale of Y axes to maximum among both of them so that both scale are equal.
I have following code but it only works for 1st time and the next time scale is not reset again before the next selection.

Sub AdjustYAxis2()

Dim cht1 As Chart
Dim cht2 As Chart
Dim maxVal As Double

Set cht1 = ActiveSheet.ChartObjects("Chart 2").Chart
Set cht2 = ActiveSheet.ChartObjects("Chart 5").Chart

If cht1.HasAxis(xlValue) And cht2.HasAxis(xlValue) Then
    maxVal = Application.WorksheetFunction.Max(cht1.Axes(xlValue).MaximumScale, cht2.Axes(xlValue).MaximumScale)

    cht1.Axes(xlValue).MinimumScale = 0
    cht1.Axes(xlValue).MaximumScale = maxVal

    cht2.Axes(xlValue).MinimumScale = 0
    cht2.Axes(xlValue).MaximumScale = maxVal
Else
    MsgBox "One or both of the charts do not have a Y-axis."
End If

End Sub

As an example, 1st screenshot is before vba code runs. and 2nd image is where the scale sets to 45 as max value for both which is what I want. But when the values in source data change next time the max value remains at 45 and is not reset.

Any suggestions to edit the code?

Thanks

 

[spgexcel]

I think I found the solution.
Sub AdjustYAxis2()

Dim cht1 As Chart
Dim cht2 As Chart
Dim maxVal As Double

Set cht1 = ActiveSheet.ChartObjects("Chart 2").Chart
Set cht2 = ActiveSheet.ChartObjects("Chart 5").Chart

[I][B]    cht1.Axes(xlValue).MaximumScaleIsAuto = True[/B][/I]
[B][I]    cht2.Axes(xlValue).MaximumScaleIsAuto = True[/I][/B]

If cht1.HasAxis(xlValue) And cht2.HasAxis(xlValue) Then
    maxVal = Application.WorksheetFunction.Max(cht1.Axes(xlValue).MaximumScale, cht2.Axes(xlValue).MaximumScale)

    cht1.Axes(xlValue).MinimumScale = 0
    cht1.Axes(xlValue).MaximumScale = maxVal

    cht2.Axes(xlValue).MinimumScale = 0
    cht2.Axes(xlValue).MaximumScale = maxVal
Else
    MsgBox "One or both of the charts do not have a Y-axis."
End If

End Sub

" cht1.Axes(xlValue).MaximumScaleIsAuto = True
cht2.Axes(xlValue).MaximumScaleIsAuto = True"

I added these two lines. It works perfectly for me..

Eureka!

 

[Jon Peltier]

Here's a way that doesn't require vba.

Here's a simple example.

Top: Data and charts with different autoscaled chart limits.

Middle: Dummy data range with minimum and maximum Y-axis values. Minimum is hard-coded zero, maximum is formula like =MAX(y values for charts 1 and 2). Copy this data, paste special in each chart as new series (orange lines). Since both charts now have the same maximum values, the axes will autoscale the same.

Bottom: Hide the new series: format to use no line.