Workbook/worksheets protected- can I allow tab re-naming?

[Roady]

Hi:

I protected my workbook so that people cannot delete sheets. However, I want
to allow them to re-name sheets. The way that I did it doesn't seem to allow
both. Can you help? thank you!

 

[Gord Dibben]

Something similar to this not too well error-trapped macro.

Sub rename_sheet()
On Error GoTo endit
newname = InputBox("type a name")
If newname <> "" Then
ActiveWorkbook.Unprotect
ActiveSheet.Name = newname
End If
ActiveWorkbook.Protect
Exit Sub
endit:
MsgBox "not a valid name. try again"
ActiveWorkbook.Protect
End Sub


Gord Dibben MS Excel MVP

 

[Roady]

Thank you, that works great! However, is there anyway to alter the verbiage
in the pop up box that appears when the user tries to click on the tab
directly to rename it. Right now, it defaults to saying, "workbook is
protected and cannot be changed". I would like to be able to have it say
something like, "If you wish to re-name the tab, please use macro button
located on top right of worksheet"

Thanks again!
R