Subscript out of Range - CodeName

[Richard Reye]

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

ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).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

 

[Nigel]

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

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

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

 

[Chip Pearson]

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

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

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

 

[Tom Ogilvy]

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

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

--
Regards,
Tom Ogilvy

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

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