Renaming within VBA

[Brent McIntyre]

Good evening all,

I was wondering if anyone knows how to programmatically
set the (Name) of objects such as Worksheets, renaming
them, ie setting the displayed name that you see when you
look at the worksheet is easy, but to reset the actual
object name I have been unable to achieve.

Any help that you may be able to provide would be much
appreciated.

Yours sincerely,

Brent McIntyre

 

[kiat]

I wonder if you are refering to the CodeName of a worksheet. This property
can only be set during design time in the worksheet's properties window.

 

[Ron de Bruin]

Is possibe like this

Sub test()
With ActiveSheet
..Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "ron"
End With
End Sub

 

[Tom Ogilvy]

You are partially correct that the codename property is read only, but the
property of the properies collection can be changed and will be reflected as
a change to the codename.

Chip Pearson provides general information on working with the VBE:
http://www.cpearson.com/excel/vbe.htm

The second half of this page describes changing the codename property
programmatically
http://www.cpearson.com/excel/codemods.htm




Here is a post by Chip on changing the codename programmatically:

From: Chip Pearson ([email protected])
Subject: Re: Codename
View: Complete Thread (18 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 2000-10-02 14:04:47 PST

Just adding on to Charles' correct answer....

You can also change the CodeName from VBA code, but I wouldn't recommend
that you make a regular practice of doing such things.

ThisWorkbook.VBProject.VBComponents("Sheet1") _
.Properties("_CodeName").Value = "NewCodeName"

For what it is worth, you can also change the code name of ThisWorkbook, but
that, too, isn't recommended.

ThisWorkbook.VBProject.VBComponents("ThisWorkbook") _
.Properties("_CodeName").Value = "MyWorkbook"

Once you change the codename of ThisWorkbook, the ThisWorkbook object is
still defined to VBA, but the item VBComponents("ThisWorkbook") is not. Use
this with great caution.

See http://www.cpearson.com/excel/codemods.htm and
http://www.cpearson.com/excel/vbe.htm

for more details. Unless you have a good reason for changing CodeNames, you
are best advised to leave them alone.

 

[Brent McIntyre]

Good morning all,

Thank you very much for your help, this is exactly what I
was after.

Yours sincerely,

Brent McIntyre