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

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


"?B?WnVsdWRvZ00=?=" wrote:

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

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

"keepITcool" wrote:

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


"?B?WnVsdWRvZ00=?=" wrote:

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

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

HTH

Regards
Pascal

"ZuludogM" a écrit dans le message de
...
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

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


"papou" wrote:

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

HTH

Regards
Pascal

"ZuludogM" a écrit dans le message de
...
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