How to make Zero's line up on Chart with 2 Y Axes

[Grothgar]

Hi All;

I have been thinking about this problem for a long time, but am no closer to coming up with a solution.

I have a report where, by changing one drop-down menu, it updates a vast variety of charts, most of which have data which uses two sets of Y Axes.

However, if I change the scales on either axis to make X axis cross at the Zero point on both sides, this only works for that particular option, as soon as I change the option, the chart is worthless.

So, what I am asking for you help in, is for some VBA code which will automatically change the max, min and major unit of the charts, so that everything is much clearer as to how the two sets of data relate (which is hard when the X Axis crosses on Y axis at 0, and the other at -30!)

If you need anymore information, please let me know.

Thanks

 

[Jon Peltier]

Here's some code, which you'll have to hook into your workbook:

Align X Axis to Y=0 on Two Y Axes

 

[Grothgar]

Thanks for that... it works... although I really don't understand how

I have noticed that this only works on the Active Chart... if I have 8 charts, how would I make this work on all of them at once... or one after the other?

 

[Jon Peltier]

You could run something like this:

Sub AlignAllChartYAxes()
  Dim cho As ChartObject
  For Each cho in ActiveSheet.ChartObjects
    cho.Activate
    AlignY 1 ' pick value from 1 to 4 that fits all charts best
  Next cho
End Sub

 

[Grothgar]

I like it

Thanks for your help!