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").Val ue
= "NewName"

or


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

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 wrote:

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").Val ue
= "NewName"

or

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

--

Dave Peterson

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


-----Original Message-----
Andy wrote:
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

.

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

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


-----Original Message-----
Andy wrote:
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

.

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

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


-----Original Message-----
Andy wrote:
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

.



.