Worksheet codename

[Andy]

On previously asking for help on renaming the object name
of a worksheet I got the responce(s) below but can't get
either to work, I know the codename property is correct
as I can view the object name in a message box, I just
can't rename it. Can anyone offer me some further help.
Ta
Andy

(This one requires Extensibility library ref)
ThisWorkbook.VBProject.VBComponents.Item
(ActiveSheet.CodeName).Properties("_CodeName").Value
= "NewName"

or


ThisWorkbook.VBProject.VBComponents
(ActiveSheet.CodeName).Name = "NewName"

 

[Chip Pearson]

Andy,

The code should work as written. Specifically what problems are
you having?


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

 

[Bill Manville]

On previously asking for help on renaming the object name
of a worksheet I got the responce(s) below but can't get
either to work

Both work for me (Excel 97).
I assume you have the relevant worksheet active?
What error do you get?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Dave Peterson]

Any chance you ran it twice against two different worksheets in the same
workbook?

And if I stepped through this code with F8's, it does the rename of the
codename, but if the codename got renamed (not already NewName), I got a "can't
enter break mode at this time" error message.

 

[Andy]

I am using excel XP and I get the following error:

Run-time error '1004':
Programmactic access to visual basic project is not trusted

I have tried both running from within the sheet object and
as a module, neither work. My code is exactly as below.

Sub Macro1()

ThisWorkbook.VBProject.VBComponents
(ActiveSheet.CodeName).Name = "NewSheet"

End Sub


Thanks in advance
Andy

 

[Chip Pearson]

Andy,

You need to enable access to the VBProject. Go to the Tools menu,
choose Macros, then Security. In that dialog, choose the Trusted
Publisher tab and check the "Trust access to Visual Basic
Project".
I

 

[Andt]

Ta very much
Works fine now

-----Original Message-----
Andy,

You need to enable access to the VBProject. Go to the Tools menu,
choose Macros, then Security. In that dialog, choose the Trusted
Publisher tab and check the "Trust access to Visual Basic
Project".
I




.