Reference sheet by number or name

[Bassman62]

In Excel2007;
In past threads I've read conflicting comments re. referencing sheets by
number or code name.
In VBA, how can I refer to the same sheet every time regardless of the
sheets code name or position?
Note the name or position may change often.
Thanks.

Dave

 

[Jim Rech]

Names, indexes and codenames can all be changed. Since codenames are least
likely to be changed by the user I'd use them.

--
Jim
| In Excel2007;
| In past threads I've read conflicting comments re. referencing sheets by
| number or code name.
| In VBA, how can I refer to the same sheet every time regardless of the
| sheets code name or position?
| Note the name or position may change often.
| Thanks.
|
| Dave
|

 

[Jim Thomlinson]

There are 3 ways to refer to a sheet; by index number, by tab name or by code
name.

Index number is inherantly dangerous as the index of the sheet chan change
based on things that your end user can do such as deleting existing sheets.

Tab name is inherantly dangerous as your code will crash if the user changes
the name of the sheet.

Code name is darn near fool proof. There is nothing that the average and
user can do to change the code name. Additionally the code name refers
directly to the object so your code is shorter and easier to write.

 

[Bassman62]

Thank you.
I had mistaken 'Tab' for 'Code Name'.
I see now that I can change the (Name) in the properties of the object
without affecting the Tab identifier. I presume this to be the 'Code Name'.

How then do I refer to the Code Name within the code?

Dave

 

[Bassman62]

Jim,

I think I've got it. I was trying to refer to it as a Worksheet or Sheet but
it looks like I can simply refer to it as I would a variable.
Thank you very much.

Dave

 

[Jim Thomlinson]

Note that refering to the sheet directly you get the intellisence drop down
as the object know its properties and methods. If you use the tab name then
you could be refering to a worksheet or a chart sheet. That being the case
you loose the intellisence as the nature of the sheet will not be know until
run time.