Alternate way to refer to sheets???


R

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
 
Ad

Advertisements

M

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
 
Ad

Advertisements

P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top