Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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...

VBA Code:
Private Sub Worksheet_Activate()
ThisWorkbook.Protect "yourpassword"
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect "yourpassword"
End Sub
 
Upvote 0
Solution
Come to think of it... Is just hiding the sheet not an option?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top