Prevent changes to specific worksheet (VBA)

[Retroshift]

Hi, I would like to disable the option to delete, move, copy or rename one specific worksheet in a workbook, regardless of the activated worksheet in the workbook. I cannot protect the workbook structure, because I want to still be able to delete other worksheets in this workbook without the use of a password. I have this very basic VBA code, which does not seem to work. Anyone has an idea on how to make this code work?

Private Sub DisableWorksheetChanges()

ThisWorkbook.Worksheets("NameOfSheet").Activate

Application.CommandBars.FindControl(ID:=847).Enabled = False 'Disable "delete worksheet" option
Application.CommandBars.FindControl(ID:=848).Enabled = False 'Disable "move or copy worksheet" option
Application.CommandBars.FindControl(ID:=889).Enabled = False 'Disable "rename" option

End Sub

 

[Jimmypop]

Hi there

Why don't you try the below... Only works when sheet is clicked or active... other sheets can still be deleted but when you right click on sheet then Excel should see it as active and will then apply the code... did a quick test On it.

Code goes into the Sheet code for the sheet you do not want moved, deleted...

Private Sub Worksheet_Activate()
ThisWorkbook.Protect "yourpassword"
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect "yourpassword"
End Sub

 

[Jimmypop]

Come to think of it... Is just hiding the sheet not an option?

 

[Retroshift]

Hi Jimmypop. Thanks for your answers. Hiding is not an option as other users will have to use the sheet.
Your code seems to do the job, since only the structure of this sheet is protected, and the content of the sheet is still editable.
That is what I wanted. Thanks. Marked as solved.

 

[Jimmypop]

Glad to help and thanks for the feedback...