lock worksheet name

[orsm6]

I have a workbook that contains easily 50 - 100 sheets. How can I stop anyone from changing any of the worksheets names?
BUT how can I do this without using the Protect Workbook option in toolbar. and hopefully without a separate macro for every single sheet.

TIA.

 

[6StringJazzer]

Protect Workbook for Structure is the textbook way to do this. Why do you need to avoid this?

The only other way is by using VBA, and there is no built-in way to capture a sheet name change so it would be a little complicated. The code would have to keep some sort of table of sheet names and if there was a change to a sheet, check to see if that change was to rename it. On the upside you would just need one Sub in ThisWorkbook, not a separate macro for each sheet.

 

[orsm6]

Protect Workbook for Structure is the textbook way to do this. Why do you need to avoid this?

The only other way is by using VBA, and there is no built-in way to capture a sheet name change so it would be a little complicated. The code would have to keep some sort of table of sheet names and if there was a change to a sheet, check to see if that change was to rename it. On the upside you would just need one Sub in ThisWorkbook, not a separate macro for each sheet.

Have been thinking about it, and agree that you're right just to use that option to use protect structure. I had thought enabling that would prevent other functions, but it seems that it doesn't.

thanks for your reply and feedback

 

[johnnyL]

Protect workbook = prevent other users from viewing hidden worksheets, adding, deleting, hiding, moving, and renaming worksheets.

@orsm6 If you feel that solution was not your desired intention, you can use macro code to toggle off the 'delete' option of a worksheet, and eliminate the renaming of a worksheet.

 

[orsm6]

Protect workbook = prevent other users from viewing hidden worksheets, adding, deleting, hiding, moving, and renaming worksheets.

@orsm6 If you feel that solution was not your desired intention, you can use macro code to toggle off the 'delete' option of a worksheet, and eliminate the renaming of a worksheet.

Ta mate - i think for now it has done enough. If people keep leaving the workbook unprotected then i might look at a code.

Would rather not have to protect everything by passwords, can be a pain to remember them lol