Worksheet Name

[solotoo]

What is the difference between *Name* and *(Name) * in the Workshee
properties.
I can define, change and goto sheets with the *name* option but canno
seem to do the same with the *(Name) * property.
-eg. - In a new spreadsheet when I look at the Excel Objects page i
the VBA screen the three default pages are listed as Sheet(Sheet1)
Sheet2(Sheet2), Sheet3(Sheet3).
If I rename a sheet on the sheet tabs, then it is just the name i
brackets which is changed. How can I change the other name by code -
can type it in the properties.

Thanks

 

[Vasant Nanavati]

This is the CodeName property and cannot be changed through code. It is
often used to refer to worksheets so that your macro doesn't break if the
user changes the tab names.

 

[Dave Peterson]

hmmmm.

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

(From a Chip Pearson post (a longggggg time ago).)

 

[Vasant Nanavati]

You're right <g> ... I forgot about doing it by programming the VBE.

Regards,

Vasant

 

[Chip Pearson]

You can also do it by accessing the Name property of the
VBComponent.

ThisWorkbook.VBProject.VBComponents("Sheet1").Name =
"NewCodeName"



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

 

[Dave Peterson]

One thing I liked about your post from a longggggg time ago was that I didn't
really need to know the name of the worksheet or the codename of the worksheet
to start.

Sub aa()
Dim wks As Worksheet
Set wks = ActiveSheet
ThisWorkbook.VBProject.VBComponents(wks.Name).Properties("_CodeName").Value _
= "NewCodeName2"
End Sub

 

[solotoo]

That's great everyone, thankyou very much!!!