Preventing sheet name changes (or a workaround)

[Guest]

We are creating a workbook that will need to activate several different
sheets and perform some calculations. Is there a way to address the
following:

Sheet name changed by user - macro looking for sheet name on tab
Sheet reordered in workbook

It seems as though I saw a way to define a "hidden" name for the worksheet
that the users couldn't see, but I can't seem to find it. Does this exist?
If so, how would I use it?

Thanks,
Barb Reinhardt

 

[Guest]

go into the VBE and look at the project window. You will see each sheet has
two names. One is the tab name, the other is the "codename"

sheet1.Range("A1").Value = 1

uses the code name. Although this can be changed, it is significanly less
likely that a user would do this.

 

[NickHK]

Barb,
You are looking fir the .Codename of the WS. The user cannot change it.
It works like an object, rather than a name:
WS_WithCodeName.Range("A1").Value=NewValue


NickHK

 

[Guest]

What you want to do is to use the sheet codename, not the sheet tab name. In
the VBE Properties for each sheet there is a (Name) and a Name. The first one
(Name) refers to the code name and has nothing to do with the tab name. In
the VBE Project Exploere wyou will see all of the sheets listed something
like this...

Sheet1(Tab This)
Sheet2(Tab That)
....

You can change the code name of the sheet by changing the Value of (Name) in
properties. I would recommend you do this as it makes your code much easier
to read. Do so for each sheet in the book. It takes a minute but it saves
time, effort and confusion down the road. Note the project explorer will look
like this

shtThis(Tab This)
shtThat(Tab That)
....

In code you can refer dircetly to the sheet by referencing the code name

msgbox shtThis.Name

One nice advantage to doing this is that intellisence will work with
shtThis, where as it will not work with Worksheets("Tab This")...

 

[Guest]

Thanks everyone for your assistance. That really helps clean up my code.

 

[Guest]

How would I use the code name in a formula.

I have something like this now:

Cells(i, "E").FormulaR1C1

Let's say the Sheet code Name is BarbsTest

 

[Guest]

AND ... how do I reference the sheet code names in formulas.