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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >