Changing worksheet CodeName generates error(s)

Reference: http://www.excelforum.com/excel-prog...-not-name.html

I delete sheet1, copy sheet3, rename to sheet1 and then attempt to rename the CodeName to Sheet1 as well.
This worked initially with out errors.

Now on the first run of the macro it generates an error:

Run-time error '32813':
Method 'name' of object '_VBComponent' failed

This fails to rename the CodeName of "Sheet1" (.Name)
Run it again and it renames the CodeName but generates yet another error:

Run-time error '-2147221080 (800401a8)':
automation error

FYI
If you really need to know, a mild case of OCD drives me to do these things.

Are you deleting the sheet with CodeName Sheet1, or a sheet named Sheet1? Look in the Project Explorer window.

Also, you need to tick Trust access to the VB project

Deleting Sheet1 which is both .Name and .CodeName "Sheet1", copying Sheet3, renaming to Sheet1 and then renaming CodeName to Sheet1.

"Trust access to the VB project" is this under Tools, Macros, Security?

I managed to climb over the OCD of this and going that route, but now Im curious too. LOL
When I wrote the code it ran fine, several times. then it started generating errors.

The automation error is not due to the CodeName change!
It is generated by simple sheet renaming.

Please Login or Register  to view this content.

When I disable and enable displayalerts the automation error is generated.
If I just go throught the "Prompts" of deleting the worksheet I do not get the automation error.

Doh!......

FWIW, I can run that repeatedly (commenting out the form show bit) without error. Have you tried adding a DoEvents line before changing the codename in case it's a simple timing issue?

Originally Posted by romperstomper

FWIW, I can run that repeatedly (commenting out the form show bit) without error. Have you tried adding a DoEvents line before changing the codename in case it's a simple timing issue?

Thanks for the info.