Macros created in Excel for MAC 2011 work in Excel 2007 but not in Excel 2013?

Hi guys,

I have written some vba code for a quotation calculator. This was my first time using macro and vba, so it was quite a challenge. After two weeks I got it to work perfectly on my MAC, but once I sent it out for my team to test, I realise that the macros do not work on some newer versions of Excel.

Can someone please help me to fix this and get it to work on all Excel versions?

Firstly, I have a start sheet asking people to enable macros, and this disappears once they do so. It also includes a code that can protect and unprotect the worksheets. These codes seems to work on all Excel versions:

Please Login or Register  to view this content.

Next I have code that when a button is pressed, a new table and button is created. This works on Excel for MAC 2011 and Excel for Windows 2007, but not any newer versions... In Excel 2013, when the button is clicked, it says, "Run-time error '1004': Unable to get the buttons property of the Worksheet class".

This is the code and I have underlined where is gets stuck when I press debug:

Please Login or Register  to view this content.

Automatically Sheet 3 is highlighted on the left hand side under Microsoft Excel Objects, even though the active sheet is Sheet 1. If I close the debugger, I am automatically on Sheet 3 (which has no buttons and no macros as of yet). If I go back to Sheet 1 and press the button a second time it works??? It creates the table and new button. If I press it a third time, it does not work and throws up the same error and takes me to sheet 3. If I press it a forth time it works again, and so on?

I have tried to replace the 'ActiveSheet' with 'Sheet 1' and I get this new error, "Run-time error '1004': Method 'Range' of object'_Worksheet' failed". Now it goes all the way through to my Add Button sub, and gets stuck here:

Please Login or Register  to view this content.

It once again highlights Sheet 3 in the debugger, and upon closing the debugger, I am once again on Sheet 3. If I click on Sheet 1 again and press the button it works. Once again it works on even number of clicks, but not on odd number of clicks???

I have tried deleting all other worksheets except for Sheet 1 and the start 'Enable Macros' sheet. But I get the same errors. The only difference being that it doesn't take me to Sheet 3 upon closing the debugger.

I have even tried replacing the button, by creating a new button in Excel 2013. But nothing seems to solve the problem.

Now if you click on the newly created, by the above code, Add Wall button, on the first click it DOES WORK and adds a new row to the table. But on the second click it throws up this error, "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found". The forth time it works, and the fifth it doesn't, and so on... Here is the code and the point at which it gets stuck is underlined:

Please Login or Register  to view this content.

If I change 'ActiveSheet' to 'Sheet1' in this part of the code, I can click the button, no errors appear, the screen moves for a second, but in the end nothing happens?

I have tried adding in error handling, and this just stops the run-time error pop up from showing up, but the buttons only ever work every second click, which still is not good enough.

Please forgive me if I haven't written my code in the correct format, I am just not sure how to???

Please can someone help me solve this ASAP!

Thank you!

Originally Posted by macropod

Cross-posted at: http://www.mrexcel.com/forum/excel-q...ws-2013-a.html
Please read the Cross-Posting policy in rule #8: http://www.excelforum.com/forum-rule...rum-rules.html

I am sorry, I have posted this question on two other forums:
http://www.ozgrid.com/forum/showthread.php?t=198940
http://www.mrexcel.com/forum/excel-q...ws-2013-a.html

Only because I am desperate for help as I need to finish this project soon. I did not mean to break forum rules on any of the forums!