Sheet Names

[SimonP]

In the VB Editor every sheet in a workbook has '2' names i.e. Sheet1 (Sheet1)
The 'name' in brackets refers to the name that you can change in excel by right clicking a sheet tab and choosing 'rename'.
The first 'name' is the (name) property used in the VB editor.
My question is : how can this first name property be changed or set through code?

Thanks
Simon

 

[Barry Katcher]

What is your purpose for wanting to do that? The following macro activates Sheet 1, which I had renamed "New Name".

Sub GotoSheet1()
Sheets("New Name").Select
End Sub

Apparently, one can use the user-defined name in code.

 

[SimonP]

Ok, let me try to explain a little further.
I want to be able to create a new sheet and then rename it (using code) so that I can refer to it later in code (as you point out you can do), even if the user changes the name of the sheet 'tab'. Hence, the need to change the sheet (Name) property via code - I know how to do it manually in the VBE, but not by code.
Obviously, activesheet().name = "whatever" just changes the 'tab' name, not the (Name) property of the sheet.
Simon
This message was edited by SimonP on 2003-02-02 11:52

 

[Jay Petrulis]

Hi,

I don't think this is possible to do during runtime. You are trying to change the CodeName rather than the Name.

From the help on the CodeName property:<code>Returns the code name for the object. Read-only String.

Note The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.

Remarks

The code name for an object can be used in place of an expression that returns the object. For example, if the code name for worksheet one is "Sheet1", the following expressions are identical:

Worksheets(1).Range("a1")
Sheet1.Range("a1")

It’s possible for the sheet name to be different from the code name. When you create a sheet, the sheet name and code name are the same, but changing the sheet name doesn’t change the code name, and changing the code name (using the Properties window in the Visual Basic Editor) doesn’t change the sheet name.</code>

You will have to refer to the sheets by their existing codenames or their Index numbers if you want to avoid referenceing them by their tab names.

_________________
Bye,
Jay

Edit (NO): Wide screen trimmed.
This message was edited by NateO on 2003-02-02 16:54

 

[Jack in the UK]

Hi

OK Excel names sheets by default and that name stay regardless; this will be Sheet1, 2, 3. Now that’s stuck fast

You can rename sheets and this name might be Report this is viewed in the tab as such. Now the Sheet default name can be edited in VBE but only as long as that sheet number is not used.

OK so where this going?

You can under the ruling of VBE methods call either name

Does that clear the issue for you?


Jack in the UK

 

[NateO]

Howdy Simon, you can try something like the following to create a new sheet and change both the sheet name and code name. Note, the codename must not include spaces ('tis illegal :wink: ).<pre>
Sub Chngs()
Dim mySht As Worksheet, y As String
y = "My_New_Sheet" 'Name your sheet
Set mySht = Sheets.Add(, Sheets(Sheets.Count)): mySht.Name = y
mySht.Parent.VBProject.VBComponents(mySht.CodeName) _
.Properties("_CodeName").Value = y
'MsgBox mySht.Index 'work with mySht
End Sub</pre>

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-02-02 17:04

 

[Jay Petrulis]

Hi Nate,

Cool!!! Nice job.

 

[NateO]

Hi Jay,

Cool!!! Nice job.

Thanks! Actually, this can be streamlined a touch as well:

<pre>
Sub Chngs2()
Dim mySht As Worksheet, y As String
y = "My_New_Sheet" 'Name your sheet
Set mySht = Sheets.Add(, Sheets(Sheets.Count)): mySht.Name = y
ThisWorkbook.VBProject.VBComponents(mySht.CodeName).Name = y
'MsgBox mySht.Index 'work with mySht
End Sub</pre>

As usual, another way to pluck a duck in Excel.

 

[SimonP]

Guys,
Thanks for all the contributions & Nate thanks very much for that code - thought it must be possible somehow!
Simon