Alternate way to refer to sheets???

[Robert Crandal]

My VBA code always assumes that the names of my sheets
will never change. However, if a user changes the name
of "Sheet1" to something else (such as "mysheet", for example),
then my VBA will not be able to find the sheet formerly
known as "Sheet1".

Is there a solution to this problem?? Is there an alternate
way to refer to a sheet other than by the label name on the
tab??

thankx

 

[marcus]

Hi Robert

Each sheet will have a number assigned to it. Sheet1 will be 1
sheet2, is 2 no matter what you call sheet2.

Worksheets(2).Activate

The above will activate the sheet no matter what the name of "Sheet2"
becomes.

Take care

Marcus

 

[Per Jessen]

Using worksheet index numbers is not a good solution, as user can change the
the order of the sheets or insert new sheets.

Worksheets(2) will always refer to the worksheet placed as second Tab.

You can protect the workbook to prevent user from renaming and inserting new
sheets, if user do not need theese options, or you can use the VBA level
sheet names.

In the project explorer you see two sheet names for each sheet. The first is
the VBA level name which do not change if user change the workbook level
name (tab name).

Use the VBA level sheet name like this:

Sheet2.Activate

Hopes this helps.
....
Per