Changing Codename in VBA

[Dennis]

2003


If I run this code is the respective w/s ThisWorkbook code window it
works fine.

ThisWorkbook.VBProject.VBComponents(wks.CodeName).Name = "Sheet2"

But if I run the code from another module I get an error 9 Subscript
not found or out of range. I did activate the worksheet in VBA prior
to running the code.

That said, I would like to change a Copied w/s Codename because it kept
the same Codename in the previous file. I would like to change the
Codename to "Sheet2" with VBA from another module.
Is this possible?

TIA EagleOne

 

[Chip Pearson]

Your code works fine for me as long as it is called from any module within
the same VBProject. If you call it from Workbook2 and Wks refers to a
worksheet in Workbook1, the code will fail.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dennis]

Actually, I am calling from my Personal.xls but your point is still
valid.
I am still trying to conceive a workaround.

Thanks Chip.

 

[Dennis]

Actually, I am calling from my Personal.xls but your point is still
valid.
I am still trying to conceive a workaround.

Thanks Chip.

 

[laydgeur]

Hi
I knew it's a quite late reply (!!!) but an answer to this topic would have helped me and saved me couple of hours.
Anyway I found the answer by myself so here it is :
The codename is not available if you're not in the current VBA project, or if you don't have the VB Editor open.
So you have to define the current VBA project in order to access the sheets codename and do operations with it :

Set Application.VBE.ActiveVBProject = your_workbook.VBProject

And it should work fine

 

[CHatmaker]

Kudos to laydgeur!

Inspired by that post I found this also works:

Workbooks("myWorkbookName").VBProject.VBComponents("ExistingSheetCodeName").Name = "myNewCodeName"