(Name) Property Under VBA Control

Z

ZuludogM

If you select a sheet and look at the Properties, you will find two properties that name the sheet:
(Name)
Name

When a user changes the sheet name by typing into the sheet Tab, they are changing the Name property, but not the (Name) property.

Example: When you open a new workbook, Sheet1 will have properties:
(Name) = "Sheet1"
Name ="Sheet1"
When the user renames that sheet to "Revenues" the properties become:
(Name) = "Sheet1"
Name = "Revenues"

You can rename the (Name) property directly in the Properties table for the sheet, however, I would like to read (and even write) that property in VBA, but the only methods I can find are for the Name property. Anybody know how to do this?

Thanks, Mark
 
K

keepITcool

the other name is the 'CodeName' of the VBProject

in Tools/References, browse to:
Visual Basic for Applications Extensibility 5.3

checkmark it. press ok.
now open the object browser (F2)
dropdown to VBIDE and you can see all the props that you can program
...

in Excel:
in macro security(2nd tab) you must check
'Allow access to VB object model'

now you're up and running and you can start learning how to do stuff.




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
P

papou

Hello Mark
ActiveWorkbook.VBProject.VBComponents("Sheet1").Name = "NewName"

HTH

Regards
Pascal

ZuludogM said:
If you select a sheet and look at the Properties, you will find two
Click to expand...
properties that name the sheet:
(Name)
Name

When a user changes the sheet name by typing into the sheet Tab, they are
Click to expand...
changing the Name property, but not the (Name) property.
Example: When you open a new workbook, Sheet1 will have properties:
(Name) = "Sheet1"
Name ="Sheet1"
When the user renames that sheet to "Revenues" the properties become:
(Name) = "Sheet1"
Name = "Revenues"

You can rename the (Name) property directly in the Properties table for
Click to expand...
the sheet, however, I would like to read (and even write) that property in
VBA, but the only methods I can find are for the Name property. Anybody
know how to do this?
 
Z

ZuludogM

Thanks for the fish and the fishing lesson, KeepItCool.

From there; I have learned that the CodeName can be read, but not written. I only want to read it, so I am pretty happy now.

Brgds,
Mark
 
Z

ZuludogM

Merci Pascal,

Combined with KeepItCool's response, this completes the picture. Your code allows a write to the CodeName and KeepItCool's recommendation gives the read - e.g.:
X = Worksheets("Sheet1").CodeNam

Just as a follow-up. This is useful for managing worksheets where the user can create copies of sheets, rename them and delete them. If the sheet is considered a form, you can use the CodeName code to manage the forms as a collection of objects.

Brgds,
Mark
 
Top