Primary and secondary Y axis should cut X axis at same value

[goels]

Hi All,

I have a chart in excel 2010 where the primary Y axis on the left shows returns in % (plotted as a column chart) and the secondary Y axis on the right shows the cumulative return also in % (plotted as a line chart).

Now the problem I am having is that the secondary Y axis instead of cutting the X-axis at 0 (just like the primary Y axis does) cuts it at some random value. Now to remedy this I set the option 'Horizontal axis crosses at' to 0.0 but this does not seem to work and the secondary Y axis still does not cut the X-axis at 0.

Any other ideas how this problem can be remedied?

Thanks

[Andy Pope]

http://peltiertech.com/Excel/Charts/AlignXon2Ys.html

[goels]

Hi Andy,

Thanks. I incorporated the code in my sheet and can see option 4 ie input 4 to AlignY function works best for me. As I am not that conversant with VBA can I ask how this code can be modified so that whenever my spreadsheet is opened for the first time option 4 runs for a particular chart (I have several on a worksheet but just want the code to apply to one of them) automatically without having to manually run the macro?

Thanks

[Andy Pope]

You can use the Thisworkbook workbook_open event to run code on opening.

[goels]

Thanks. I have managed to trim the code down to the below:

Please Login or Register  to view this content.

The problem I am facing now is how to tell this code that always run for my particular chart. Reading a bit on the internet what I was trying is this code which should activate my chart called Chart5 (I got this name by clicking on the chart and then looking in the name box on top left) but when I run this code it gives all sorts of errors. Am I missing something here?

Please Login or Register  to view this content.

Thanks

[Andy Pope]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Just alter worksheet name from xx to whatever is relavent to your file.

[goels]

Thanks Andy.

I have also modified your code a bit to now work when opening a workbook.

Please Login or Register  to view this content.

One of the things I am noticing though is that when my workbook is generated from the system that generates it, this code seems to half work as in it aligns the axes at 0 but the secondary axis max value is somewhere on everest and so my line chart based on it disaapears half way. To workaround this I was trying to do the same but when the workbook closes. So using the code

Please Login or Register  to view this content.

When trying to run this though I get the Macros window popping up instead of the code executing. Any idea why this might be the case?

Thanks

[Andy Pope]

post example workbook that illustrates the problem

[goels]

For some reason when uploading the .xltm file it does not let me do so.

Just realised though that when I open the workbook and then close it it does update the chart but for some reason it does not work when I simply try to run the code from the VBA window!
Guess will just need to live with this mystery...

But thanks again Andy for all your help