difference of worksheet name and (name) property in VBA properties

In VBA within Excel, the worksheets have a name property and a (name)
property. One the user can change by right-clicking on the worksheet
tab and change the name. This is also the same name field with which i
know how to run a loop. My question is how to do i use the OTHER name
field (the only that can only be changed within the VBA properties
field) to run a loop?

The purpose is to run a macro loop regardless of the names of the
worksheet (i can't just lock off the worksheet names, they need to be
left open to change)

Any help is greatly appreciated.

Hi cydenney,

If you want to loop through all the worksheets regardless of name, you would
do this:

Dim ws As Worksheet

For Each ws In Worksheets
Debug.Print ws.Name '/ or other operations on ws object
Next ws

The (name) property is known as the CodeName for the worksheet. This comes
in very handy if you want to leave the worksheet open to name changes - you
can refer to the CodeName of the worksheet just like it's an object. I
typically name my worksheets with the prefix "ws". So say I apply a
CodeName of wsData to a worksheet. The user can rename the worksheet to
whatever they want, and it won't affect this CodeName. So I can refer to
that object like this:

MsgBox wsData.Name '/ gives me current (UI) name of worksheet

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]



cydenney@gmail.com wrote:
> In VBA within Excel, the worksheets have a name property and a (name)
> property. One the user can change by right-clicking on the worksheet
> tab and change the name. This is also the same name field with which i
> know how to run a loop. My question is how to do i use the OTHER name
> field (the only that can only be changed within the VBA properties
> field) to run a loop?
>
> The purpose is to run a macro loop regardless of the names of the
> worksheet (i can't just lock off the worksheet names, they need to be
> left open to change)
>
> Any help is greatly appreciated.

To elaborate on Jake's expanation (which is absolutly correct) the (Name)
refers to the sheet object, where as Name refers to the tab name which is a
property of the object. So if you have not changed the code names of your
sheets (which I agree with jake you should definitly do) then you can type a
statement like

msgbox sheet1.name

Note the intellisense will work when you type this as opposed to

msgbox worksheets("Sheet1").name

This is because Sheet1 refers directly to the worksheet object, where as
worksheets("Sheet1") referes to a sheet with the tab name Sheet1 which could
turn out to be a chart. This will not be determined until run time so
intellisense will not function as the things a worksheet can do and the
things a chart can do are very different...
--
HTH...

Jim Thomlinson


"Jake Marx" wrote:

> Hi cydenney,
>
> If you want to loop through all the worksheets regardless of name, you would
> do this:
>
> Dim ws As Worksheet
>
> For Each ws In Worksheets
> Debug.Print ws.Name '/ or other operations on ws object
> Next ws
>
> The (name) property is known as the CodeName for the worksheet. This comes
> in very handy if you want to leave the worksheet open to name changes - you
> can refer to the CodeName of the worksheet just like it's an object. I
> typically name my worksheets with the prefix "ws". So say I apply a
> CodeName of wsData to a worksheet. The user can rename the worksheet to
> whatever they want, and it won't affect this CodeName. So I can refer to
> that object like this:
>
> MsgBox wsData.Name '/ gives me current (UI) name of worksheet
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
>
> cydenney@gmail.com wrote:
> > In VBA within Excel, the worksheets have a name property and a (name)
> > property. One the user can change by right-clicking on the worksheet
> > tab and change the name. This is also the same name field with which i
> > know how to run a loop. My question is how to do i use the OTHER name
> > field (the only that can only be changed within the VBA properties
> > field) to run a loop?
> >
> > The purpose is to run a macro loop regardless of the names of the
> > worksheet (i can't just lock off the worksheet names, they need to be
> > left open to change)
> >
> > Any help is greatly appreciated.
>
>
>