Macro - Runtime Error 1004

[Evian]

Would really appreciate some help on an issue I am facing especially being very new to macros. I have tried but not been successful in getting excel to ignore the error messages or correcting the code.

Any help would be gratefully appreciated, thanks

Here goes:

I have a line chart which I created which shows data on one axis. I created a macro which when clicked by the user creates a secondary axis and shows this data on the chart as well. I also have another macro which resets the chart back to one axis and only displays data along that single axis.

It all works fine except if you click the macro button twice (either for it to show the secondary axis and data or if you click the reset button twice). I get the following error message:

Runtime Error '1004':
Unable to set the AxisGroup property of the Series class

Here's the VBA code (error code highlighted in bold):

Please Login or Register  to view this content.

[Evian]

For the reset button I get the following issue:

Runtime Error '1004':
Method 'SeriesCollection' of object' _Chart' failed

VBA Code:

Please Login or Register  to view this content.

[Andy Pope]

Does the range being plotted contain empty or error cells?

Can you post example file.

[Evian]

The range does not contain any empty or error cells.

The macro creates the secondary axis and draws data from a hidden table. What I believe it is trying to do is recreate the secondary axis a second time when you click it twice which is not necessary.

[Andy Pope]

Your code runs fine for the example workbook

[Evian]

The issue is if you click either macro button twice (don't understand why anyone would but...) it throws up that runtime error which I want it to ignore.

[Andy Pope]

Gotcha.

Add some checks to your code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Evian]

Excellent thanks, Andy.

Any chance you can explain why the error was occurring and what the checks do? Might be useful for others who experience similar issues.

[Andy Pope]

The deletion one is easy. If the chart only contains 1 series then referencing the 2nd series will fail. So check chart contains enough data series.

The AxisGroup property failing on second usage is probably a bug.
So the code checks the axisgroup valid before setting it.

[Evian]

I now have another issue:

The file works perfectly but when I sent it out to colleagues it gets zipped and when the file is opened it gets remained from:

CRT Rebate Savings Tracker September 2008.xls

to

CRT Rebate Savings Tracker September 20081.xls

The change being that the file name adds in an extra digit on the end. The implication of this is that "Subscript out of range" error occurs if you click the button twice which I believe is because the file names no longer match up.

Is there anyway to ensure that whatever the file name ends up as, that it picks this up rather than the default:

CRT Rebate Savings Tracker September 2008.xls

[Andy Pope]

No idea why the file is being renamed.

But you can modify your code so file name does not matter.

Please Login or Register  to view this content.

Please Login or Register  to view this content.