Macro has errors if Visual Basic Editor is not open

I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".

I have got the whole thing figured out and running to satisfaction, except...

The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"

Any ideas what could be casing this? The errors occur when attempting to add sheets.

Here's my "UpdateAll" macro that is having the issues:

Please Login or Register  to view this content.

It seems to break-down here:

Please Login or Register  to view this content.

When it goes to update the Main Sheet with the new worksheet's codename, ActiveSheet.CodeName seems to be blank.

Again, this only happens if the Visual Basic Editor is not open. It is as if excel cannot get the codename of a sheet if the editor is not open.

Any help would be much appreciated.

Try this routine

Please Login or Register  to view this content.