protecting a sheet

Paul B · Mar 10th, 2002, 04:04 PM

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

SamS · Mar 10th, 2002, 05:06 PM

Protect just the "Data" sheet not the whole workbook.

Mar 10th, 2002, 05:09 PM

protects the data, but won't prevent the sheet from being deleted.

Quote:

On 2002-03-10 16:06, SamS wrote:
Protect just the "Data" sheet not the whole workbook.

Paul B · Mar 10th, 2002, 05:21 PM

Yes just the sheet named "Data" sheet not the whole workbook

SamS · Mar 10th, 2002, 05:35 PM

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

RWinfield · Mar 10th, 2002, 08:39 PM

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

Paul B · Mar 10th, 2002, 09:20 PM

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

Anne Troy · Mar 10th, 2002, 10:03 PM

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.

Paul B · Mar 11th, 2002, 05:09 AM

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!

Dave Hawley · Mar 11th, 2002, 05:15 AM

Hi Paul

Seems like this has caused grief for a number of Excel users. I recenlty answered this for another person here:

http://mrexcel.com/board/viewtopic.p...1862&forum=2&2

Mar 10th, 2002, 10:03 PM	#8
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 10th, 2002, 04:04 PM	#1
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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

Mar 10th, 2002, 05:06 PM	#2
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Protect just the "Data" sheet not the whole workbook.

Mar 10th, 2002, 05:21 PM	#4
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	Yes just the sheet named "Data" sheet not the whole workbook

Mar 10th, 2002, 05:35 PM	#5
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	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

Mar 10th, 2002, 08:39 PM	#6
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	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

Mar 10th, 2002, 09:20 PM	#7
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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

Mar 11th, 2002, 05:09 AM	#9
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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!

Mar 11th, 2002, 05:15 AM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Paul Seems like this has caused grief for a number of Excel users. I recenlty answered this for another person here: http://mrexcel.com/board/viewtopic.p...1862&forum=2&2