I have a spreadsheet with VB code. The code works OK when the spreadsheet is open. However if I save and close the spreadsheet, then re-open it I get a pop-up saying just 'Automation Error' plus OK and help buttons.
automation error.JPG
Clicking on OK opens the VB editor window, the spreeadsheet is locked and the visual basic is stopped. In the VB editor project pane, the Solver addin is showing, plus my personal.xlsb, and a third project which has no label or name. Looking into that project, it has the modules with my code, the forms, but the Excel objects (sheets) are highlighted blue and are just labels sheet1, sheet 10 etc rather than the actuall sheet names
automation error2.JPG
Trying to start any of the macros, results in the 'automation error' pop-up appearing again. It is not possible to close Excel as the window is completely unselectable. Trying to close the VB editor results in a message 'the command will stop the debugger'. clicking OK, takes you to auto re-start of Excel - the same cycle again.

The only way out is to use the Task manager to stop Excel.

Now the interesting bit - if I rename my worksheet, I can then open it, and it works. However, saving the new version after using it, and then trying to open again - same problem.

any ideas?