Subscript out of Range - CodeName
 

I'm getting a 'Subscript out of range' error on a line of code as follows

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeName").Value = "Output" & Now * 10000000000#

The odd thing is the fact that this only occurs the first time the code is
run when the workbbok is opened and, when I select debug the error, all I
simply do is press run (play button) and the code conitnues on perfectly with
the new codename for the active sheet. I've also changed the 'NOW *
10000000000#' to '12' and I got the same error (I couldn't test it for the
second use as a codename conflict would occur).

Any thoughts as to why this happens and how to fix it would be greatly
appreciated. I already have tried 'On Error Resume Next' but that didn't
work. It still halted my procedure.


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone

Subscript out of Range - CodeName
 

Not sure if this helps - but i tried your code Excel 2002 SP-2 VB6.3 and
it works fine. I could not get it to throw an error either in open workbook
event or a module, repeated use did not either !

--
Cheers
Nigel



"Richard Reye" wrote in message
...
I'm getting a 'Subscript out of range' error on a line of code as follows


ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeN
ame").Value = "Output" & Now * 10000000000#

The odd thing is the fact that this only occurs the first time the code is
run when the workbbok is opened and, when I select debug the error, all I
simply do is press run (play button) and the code conitnues on perfectly
with
the new codename for the active sheet. I've also changed the 'NOW *
10000000000#' to '12' and I got the same error (I couldn't test it for the
second use as a codename conflict would occur).

Any thoughts as to why this happens and how to fix it would be greatly
appreciated. I already have tried 'On Error Resume Next' but that didn't
work. It still halted my procedure.


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then
beat
you with experience" - someone

Subscript out of Range - CodeName
 

Your code worked for me just fine. You might try shortening it to

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name =
_
"Output" & Now * 10000000000#

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Richard Reye" wrote in message
...
I'm getting a 'Subscript out of range' error on a line of code
as follows

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeName").Value
= "Output" & Now * 10000000000#

The odd thing is the fact that this only occurs the first time
the code is
run when the workbbok is opened and, when I select debug the
error, all I
simply do is press run (play button) and the code conitnues on
perfectly with
the new codename for the active sheet. I've also changed the
'NOW *
10000000000#' to '12' and I got the same error (I couldn't test
it for the
second use as a codename conflict would occur).

Any thoughts as to why this happens and how to fix it would be
greatly
appreciated. I already have tried 'On Error Resume Next' but
that didn't
work. It still halted my procedure.


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their
level then beat
you with experience" - someone

Subscript out of Range - CodeName
 

Are you doing this in the Workbook.Open event. I would try qualifying
Activesheet

ThisWorkbook.VBProject.VBComponents(ThisWorkbook.A ctiveSheet.CodeName _
).Properties("_CodeName").Value = "Output" & Now * 10000000000#

--
Regards,
Tom Ogilvy


"Richard Reye" wrote in message
...
I'm getting a 'Subscript out of range' error on a line of code as follows


ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Properties("_CodeN
ame").Value = "Output" & Now * 10000000000#

The odd thing is the fact that this only occurs the first time the code is
run when the workbbok is opened and, when I select debug the error, all I
simply do is press run (play button) and the code conitnues on perfectly
with
the new codename for the active sheet. I've also changed the 'NOW *
10000000000#' to '12' and I got the same error (I couldn't test it for the
second use as a codename conflict would occur).

Any thoughts as to why this happens and how to fix it would be greatly
appreciated. I already have tried 'On Error Resume Next' but that didn't
work. It still halted my procedure.


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then
beat
you with experience" - someone