(Name) vs Name properies for a worksheet

[rogge]

How do I change and address/access the "(Name)" property of a sheet with VBA;
this property is only 'visible' in the VBA properties window, it is not
visible in the object browser?

This property is not the same as "Name" which is also shown on the tab.

My code creates sheets during a simulation. Currently the user can delete
the programatically created sheets, but this will also delete any user
created sheets. I want to use the "(Name)" property to control which sheets
are deleted.

Pseudo code....

if sheet.name <> "KeepThisSheet0" And sheet.name <> "KeepThisSheet1" then
sheet.delete


Also, what are the differences between the objects "Sheets" and "Worksheets"


Windows XP, Excel 2007

Thank you all very much!

 

[Chip Pearson]

The Name property of a worksheet is what is displayed on the tabs and
is how the sheet is known to Excel (as in formulas). In the VBA
editor, the (Name) refers to the CodeName, which is how the sheet is
known internally to VBA. You can get the CodeName from a worksheet
object:

Debug.Print Worksheets("Sheet One").CodeName

but not the other way around (at least not directly). In most cases,
you can use the object's code name to prevent problems that might
arise if a user changes a sheet's name:

Worksheets("Sheet One").Range("A1").Value = 1234
' or
Sheet1.Range("A1").Value = 1234

The "Sheets" object contains all the sheets of all types (worksheets,
chart sheets, macro sheets, dialog sheets) while Worksheets contains
only worksheets.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

That (Name) property is called the codename.

You can manually change the codename by:
Open the workbook
Go into the VBE (alt-f11)
Show the project explorer (ctrl-r)
Select the workbook's project
Select the sheet you want
Show the properties window (F4)
And typing the new codename into that (Name) property.

You can refer to the codename in code with something like:

dim sh as object 'any kind of sheet
for each sh in activeworkbook.sheets
Select case lcase(sh.codename)
case is = lcase("keepthissheet0"), lcase("keepthissheet1")
'do nothing
case else
sh.delete
end select
next sh

Workbooks can contain worksheets, chartsheets, macrosheets, international macro
sheets, and dialogsheets.

I'm not sure why you want to change the codename via code, but if the user
allows programmatic access (tools|macro|security|trusted publishers tab in
xl2003 menus), you can use something like:

dim sh as object 'any old sheet type
set sh = activeworkbook.sheets("aaa")
ThisWorkbook.VBProject.VBComponents(sh.codename).Name = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(sh.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"

But this won't work if the user doesn't have that setting the way you need it.

 

[ryguy7272]

This is how I do it:

Sub DelAllWorksheets()
Application.DisplayAlerts = False
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Firms") Then
sh.Select False
Else
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

Sheets("Firms, Import").Activate
Range("A1:B1").Select

End Sub

That macro deletes all sheets that don't have "Firm" on the tab. Maybe you
can modify that to get the results you seek.

HTH,
Ryan---

 

[rogge]

Thank you all very much for the detailed information. It is exactly what i
wanted and needed. too bad i could only say that one answer was correct.

Also, is there any advantage to using Worksheets over Sheets to refer to
Worksheets? either performance, code legibility, experience, etc?

Currently i am using Sheets and referring only to worksheets.

-rogge