CODEWITHSUNDEEP
excelvbevba
Peter Albert
Peter Albert

Reputation: 17475

How to properly rename CodeModule of ThisWorksheet

I'm trying to rename the ThisWorkbook code module of an Excel worksheet using VBA Extensibility.

I accomplish this with the line

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "wb"

I can see the change in the VB editor and also access the workbook's properties, e.g. Debug.? wb.Name.

However: If I save and close the file and then reopen it, I get strange behavior. If the code module was empty before renaming it, it reverts back to the old empty ThisWorkbook name.

If it was not empty or was populate before the saving, I now have both, an empty ThisWorkbook module (that would fire events if there were any) - and the filled wb module - which does not fire workbook events:

enter image description here

Has anyone seen this behavior - and knows a fix/workaround?

Upvotes: 8

Views: 2647

Answers (1)

user2140173
user2140173

Reputation:

Quick answer: ThisWorkbook.[_CodeName] = "newName"

Detailed answer

When I add references to the Microsoft Visual Basic For Applications Extensibility 5.3 and run your line

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "wb"

The ThisWorkbook Name property isn't actually being modified

enter image description here

Saving and re-opening the file causes a duplication of the ThisWorkbook object

enter image description here

Which pretty much means now I have two Workbook objects within one workbook and both are named ThisWorkbook

The workaround is to rename the ThisWorkbook to wb using the Properties window or

ThisWorkbook.[_CodeName] = "newName" because ThisWorkbook.CodeName is read-only.

Upvotes: 10

Related Questions