Protect the Sheet Name

[Buce Roberson]

I am giving my spreadsheets to other users now, and I've
already run into one area that any unknowing user can
screw up a macro unintentionally.

Most of my print routines and other operations select a
sheet by name. But if a user decides to change the name of
a sheet to suit themselves, the sheet references are
fouled up.

How can I set the workbook up to protect the name on the
sheet tab without protecting the sheet itself?

Thanks,


Bruce

 

[Bob Phillips]

Bruce,

use the codename.

If you look at the explorer window for a workbook, you will see that each
sheet has two names. The name in brackets is the name that the user sees,
the first name is the codename.

You can change the codename by clicking the sheet in the explorer, and then
modify the name property in the properties window.

You access a sheet like

sheet1.Activate

rather than

Worksheets("Summary").Activate

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Hawk]

Tools -> Protection -> Protect Workbook

This will also prevent the user from deleting or moving
any sheets. This may cause an issue with your program if
your VBA code tries to rename, delete, re-orded,...any of
the sheets. The only way around this that I know of is to
unprotect the workbook in your code just before the
manipulation, and then re-protect it in your code.

thisworkbook.unprotect Password:="123"
' rename sheet
thisworkbook.protect Password:="123"

HTH...

thisworkbook.

 

[Guest]

Too bad I didn't think of this before I began writing all