protecting a sheet

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
577
Is there a way to keep for deleting a sheet from a workbook, I have a sheet named "Data" that I don't want someone to accidentally delete, protecting the workbook is not a option, sheets need to be put in and deleted all the time, just want to make sure that the "data" sheet is not deleted. maybe something like before delet? Thanks for your help
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
protects the data, but won't prevent the sheet from being deleted.

On 2002-03-10 16:06, SamS wrote:
Protect just the "Data" sheet not the whole workbook.
 
Upvote 0
should have tried it first - sounds like some vba code is needed in the workbook module, tried a few things but couldn't get it to work
 
Upvote 0
This code will grey out the delete sheet option on all toolbars and also if you right click the sheet tab. Right click on the DATA sheet tab, choose VIEW CODE from that menu, and paste this code into the VBE. Thanks to Ivan for showing me the Control ID method of deleting menu items.

Private Sub Worksheet_Activate()
ID = 847
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
End Sub

Hope this helps

Rick
 
Upvote 0
the code below makes it so you can't delete any of the sheets, I need it to work on only the one named "Data". can it be changed to do that?
Private Sub Worksheet_Activate()
ID = 847
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
End Sub
 
Upvote 0
Paul:

If you have put that code into the Workbook or into a module, it will work on all sheets (I believe--someone please correct me if I'm wrong).

You need to double-click the Data sheet in the VB editor window and paste it THERE only. You'll need to remove it from the ThisWorkbook or from a module if it is there.

Then test.

Good luck.
 
Upvote 0
I put it in the worksheet code, but its worst that I thought now I can't delete ANY SHEET IN ANY WORKBOOK, how do I change it back. I have deleted the code saved the book closed excel, restarted excel, I still can't delete ANY sheets.Help!
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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