Secondary y-axis to auto scale not working: throwing Method 'Axes' of object '_Chart' fail

Hi,
I have a about 12 charts in my worksheet. About 6 charts (Clustered column charts) of them have primary & secondary axis.
I have a macro which align the primary & secondary axes to zero. So far good.
The input of my chart changes and I want to be able to reset the axes value to auto and have a macro for that.
The code works fine for the primary y-axis, but I run into the Method 'Axes' of object '_Chart' failed for the secondary y-axis (highlighted in red and underline in the code below).
How can this be fixed?

Any ideas about how this can be fixed would be very much appreciated.

Please Login or Register  to view this content.

Thanks

Welcome to the forum!

Please see our rules regarding code tags, which I have added for you this time.

Your code worked fine for me -- as long as my test chart had a secondary axis system. If my test chart had only a primary axis system, then the code gave me that same error. You indicate that only 6 of your 12 charts have secondary axis systems. I would guess that, as your For Each..Next loop is going through each chart object, the error is triggered when it comes to a chart that has only a primary axis system.

Debugging suggestions:

1) Add a Stop statement (or breakpoint) before it enters the loop, so you can enter debug mode.
2) Add watches or debug.print statements in order to evaluate whether a given ochrtobj has secondary axis system (perhaps ochrtobj.axes.count?)
(If you are unfamiliar with these debugging tools: http://www.cpearson.com/excel/DebuggingVBA.aspx )

My guess is that the final fix will be to figure out some way -- based on what you know about all 12 chart objects -- to identify which charts have secondary axis systems and conditionally execute that block of code only when the secondary axis system exists.

I didn't expect the loop to go thorough all the charts in the worksheet.

This is what the For Each...Next loop does. If you don't want the code to loop through all of the charts in the worksheet, then you can drop the For Each...Next loop

As stated, remove the loop and just use the activechart:

Please Login or Register  to view this content.

On which line? I assume you did select a chart first?

How are you running the code? From the macros dialog, a button, or somewhere else?

Are you using an ActiveX button, or a Form button? You want the latter ideally as an activex one takes the focus when you click it by default so the chart is no longer active. If you have a real need to use activex, then set the button's takefocusonclick property to false.