I have a number of protected sheets with macros doing various things. Is it
possible to disable the sheet rename function when right clicking a sheet
tab? If a user changes the sheet name then the macros will stop working as
they will be unable to find the sheets named within them.

Thanks for your help.

Nick

Hi Nick,

you can protect the workbook to prevent user from changing name.

Regards,
Ivan

You may want to change your code to use the codename of the worksheet and let
the users rename the worksheet as they see fit.

If you look at project explorer within the VBE, you'll see the worksheets listed
under the Microsoft Excel Objects branch.

You'll see
Sheet1(whattheyseeinexcel)

The name in the ()'s is what the user sees in excel. The codename to the left
is what you can use in your code:

dim Wks as worksheet
set wks = worksheets("whattheyseeinexcel")
wks.range("a1").value = "hi"

could be replaced with:
sheet1.range("a1").value = "hi"

You could even rename the codename to something more significant (hit F4 to see
the properties of that worksheet and change the (Name) property to what you want
(still in the VBE).

=========
Alternatively, you could protect the workbook.
Inside Excel:
Tools|Protection|protect workbook|check structure

This will prevent the renaming of worksheets as well as
inserting/deleting/moving of them, too.

(Be aware that this kind of protection is easily broken.)

=========
The codename can be changed, too--but it's beyond most users.



Nick Smith wrote:

I have a number of protected sheets with macros doing various things. Is it
possible to disable the sheet rename function when right clicking a sheet
tab? If a user changes the sheet name then the macros will stop working as
they will be unable to find the sheets named within them.

Thanks for your help.

Nick

--

Dave Peterson

Nick,

In your code, simply replace occurences of

Worksheets("Sheet Name")

with the worksheet's code name. When you see the worksheet in the Project Explorer, you'll see
something like

Sheet1 (SheetName)

Where SheetName is the name on the worksheet tab. In this case, the codename is Sheet1. You can
change the code name to something that makes sense within the code by changing it in the properties
window when you have the worksheet selected in the Project Explorer.


Worksheets("Sheet Name").Range("A1").Value ....

would become

Sheet1.Range("A1").Value ....

Using this type of code allows the user to change the sheet name with no impact on your code.

HTH,
Bernie
MS Excel MVP


"Nick Smith" wrote in message
...
I have a number of protected sheets with macros doing various things. Is it
possible to disable the sheet rename function when right clicking a sheet
tab? If a user changes the sheet name then the macros will stop working as
they will be unable to find the sheets named within them.

Thanks for your help.

Nick