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

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" wrote in message
ups.com...
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

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

Thanks Chip.

Chip Pearson wrote:
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" wrote in message
ups.com...
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

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

Thanks Chip.

Chip Pearson wrote:
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" wrote in message
ups.com...
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