excel: protect tab names

[scroce]

Is there any way to prevent or detect the change of a particular worksheet's tab name? -

I have a workbook with 30 or so worksheets. I have code that makes direct reference to the names of these sheets on their tabs. I want to prevent the user from changing the name of these 3 tabs, while still allowing them to change the other 27 at will.


Is there any way to say somthing like:

OnWorkbookClose

If Sheet1.TabName <> &quot;myName&quot; then
Sheet1.TabName = &quot;myName&quot;
End if


Thanks Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[StevePB]

Hi scroce,

You more or less had it. The actual syntax would be:

Sub chkShName()

If Sheet1.Name <> &quot;MySheet&quot; Then
    Sheet1.Name = &quot;MySheet&quot;
End If

End Sub
[code]
Good luck, SteveB.

 

[scroce]

thanks - i knew is was something pretty straightforward like that.

One followup question - can i be assured that sheet1 will always remain mySheet? - I mean what if someone deletes a few sheets, adds a few more and then re-arranges the order of them? - will excel re-assign the sheet numbers and thus thwart my code?
Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[rmikesmith]

Scroce,

Here is an alternative method that will actually prevent the user from making the change rather than have your code try to react to this after the fact. Put the following procedure into a standard code module:

Sub ProtectSheet(ByVal ProtectionOn As Boolean)

  If ProtectionOn Then
    ThisWorkbook.Protect Password:="MyPassword", Structure:=True
  Else
    ThisWorkbook.Unprotect Password:="MyPassword"
  End If

End Sub

Next, for each worksheet you want to protect, create these Activate & Deactivate event procedures in its code module:

Private Sub Worksheet_Activate()
  ProtectSheet True
End Sub

Private Sub Worksheet_Deactivate()
  ProtectSheet False
End Sub

This strategy will enable Workbook-level protection when the chosen worksheets are active, preventing renaming or deletion of those worksheets. When one of the other 27 sheets is selected, the protection is removed. Do keep in mind that this will also prevent the user from inserting new worksheets while one of the special worksheets are active.

Hope this helps.
M. Smith

 

[StevePB]

rmikesmith's solution is much more resilient than mine, but to answer your question, Sheet1 will always be called Sheet1 even if it is moved within the workbook. However the index will change if the sheet is moved e.g. sheets(1) could become sheets(2) etc.

Regards, SteveB.

 

[scroce]

thanks rmike. That is a really creative solution. don't think i would have thought of that one. Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects