Worksheet Object Name
 

On creating a new worksheet I can set the name of it (the
text shown on the tab) but I can't set the object name.
The properties of the sheet contain a object name property
shown as "(name)" which I cant grab hold of in VBA. Can
anyone help

Worksheet Object Name
 

The CodeName preoperty is read-only but can be changed through the VBE.

ThisWorkbook.VBProject.VBComponents. _
Item(ActiveSheet.CodeName).Properties("_CodeName") .Value = "NewName"

For this to work, you need to set a reference to the VBE Extensibility
Library.

--

Vasant




"Andy" wrote in message
...
On creating a new worksheet I can set the name of it (the
text shown on the tab) but I can't set the object name.
The properties of the sheet contain a object name property
shown as "(name)" which I cant grab hold of in VBA. Can
anyone help

Worksheet Object Name
 

ThisWorkbook.VBProject.VBComponents. _
Item(ActiveSheet.CodeName).Properties("_CodeName") .Value = "NewName"

This can be shortened to

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = "NewName"

No reference to the Extensibility library is required.


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


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
The CodeName preoperty is read-only but can be changed through the VBE.

ThisWorkbook.VBProject.VBComponents. _
Item(ActiveSheet.CodeName).Properties("_CodeName") .Value = "NewName"

For this to work, you need to set a reference to the VBE Extensibility
Library.

--

Vasant




"Andy" wrote in message
...
On creating a new worksheet I can set the name of it (the
text shown on the tab) but I can't set the object name.
The properties of the sheet contain a object name property
shown as "(name)" which I cant grab hold of in VBA. Can
anyone help

Worksheet Object Name
 

Thanks for the correction, Chip!

--

Vasant


"Chip Pearson" wrote in message
...
ThisWorkbook.VBProject.VBComponents. _
Item(ActiveSheet.CodeName).Properties("_CodeName") .Value = "NewName"

This can be shortened to

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = "NewName"

No reference to the Extensibility library is required.


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


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
The CodeName preoperty is read-only but can be changed through the VBE.

ThisWorkbook.VBProject.VBComponents. _
Item(ActiveSheet.CodeName).Properties("_CodeName") .Value = "NewName"

For this to work, you need to set a reference to the VBE Extensibility
Library.

--

Vasant




"Andy" wrote in message
...
On creating a new worksheet I can set the name of it (the
text shown on the tab) but I can't set the object name.
The properties of the sheet contain a object name property
shown as "(name)" which I cant grab hold of in VBA. Can
anyone help