Worksheet Tabs

[Mldeuser]

Is it possible to lock the tabs so they can not be renamed??

Thanks
Mark

 

[Richard Schollar]

hI

Tools>Protection>Protect Workbook. You can apply a password to this too but please note that workbook protection (like worksheet protection) is not terribly robust and will prevent no obstacle to a determined user (but will serve to deter causal users from changing your sheet names).

 

[lenze]

Just a thought. Are you requesting this because you use the Sheet names in code and when they are changed, the code errors? If so, you can avoid this by using Index numbers in your code instead of Names. So instead of

WorkSheets("Mary").Range....

use

WorkSheet(3).Range... or
Sheet3.Range...

Now it will usually not matter if a user renames a sheet.
lenze

 

[Mldeuser]

That was the reason. I will try what you suggested

Thanks

 

[Richard Schollar]

An even more secure way would be to use the sheet's code name in code, as the index will potentially change if a user moves a sheet, or deletes/inserts one.

The sheet's code name is the name in the VBE in the Properties Window of the sheet next to the (Name) field (so you can also change it in this window too). Somewhat confusingly, it is also the name that doesn't appear in brackets in the top left Project Window of the VBE for the sheet. If you use that in code, then you won't have to worry about users changing order or sheet names from the user interface (ie Excel itself) as the code name does not change:

'refer to sheets via CodeName:

Sheet2.Range("A2:A10").Formula = "=RAND()"

'text in red above is the code name (as determined from the properties window of the worksheet in question)

 

[Mldeuser]

Thanks for that bit of information as some of the user do reorder the worksheets.

Mark