how to prevent a worksheet from being deleted?

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Aug 17, 2002

• #2

I don't think there's an event to trap this. The easiest way might be just to protect the workbook when that particular sheet is activated and unprotect it when a user selects another sheet. Right-click on the sheet select 'View Code' and copy and paste the following into the VBA editor. HTH.

Code:

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

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

 

Upvote 0

Ivan F Moala

MrExcel MVP

Joined

Feb 10, 2002

Messages

4,209

• Aug 17, 2002

• #3

On 2002-08-17 02:53, muffins wrote:
hi everyone,

may i know how do i prevent a certain worksheet from being deleted? how to do it programatically? (i do not want to protect my workbook)

many thanks in advance.

Click to expand...

Hi muffins
One way to do this, although NOT infailable is
to place this code in the sheets code module that you want to protect.

All it does is to Disable ALL references to the [Delete sheet] command avialable via
[Edit] > [delete sheet] or Right click Tab > [Delete Sheet] or any where else this command resides....even if you insert the command via customize etc.

<pre/>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CommBarTmp, Commbar As CommandBar

For Each Commbar In Application.CommandBars
Set CommBarTmp = Commbar.FindControl(ID:=847, recursive:=True)
If Not CommBarTmp Is Nothing Then CommBarTmp.Enabled = False
Next
End Sub
</pre>

 

Upvote 0

Ivan F Moala

MrExcel MVP

Joined

Feb 10, 2002

Messages

4,209

• Aug 17, 2002

• #4

Oh BTW

Reset this before closing
Just set the Enabled to True

 

Upvote 0

A

Askar

New Member

Joined

Jul 30, 2002

Messages

12

• Aug 17, 2002

• #5

Hi Ivan,

This really works great, but how about disabling others options like "Rename"& "Move or Copy...".

I don't know the ID's.

Askar

 

Upvote 0

S

stevebausch

Well-known Member

Joined

May 11, 2002

Messages

810

• Aug 17, 2002

• #6

Huh....?

You are worried about "protecting the workbook? What's the problem if you unlock all the cells, and then protect the workbook? If you do that, the sheets can't be deleted, renamed, moved, etc.

However, the users can still modify the sheet to their heart's content; they just can't delete it.

 

Upvote 0

M

muffins

Board Regular

Joined

Jun 18, 2002

Messages

86

• Aug 19, 2002

• #7

wow thanks!! and how do i disable the View Code option as well?

 

Upvote 0

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Aug 19, 2002

• #8

You can password protect your code, to stop people viewing/ changing it- click on Tools- VBA Project Properties and the Protection tab (you will need to save before this takes effect).

 

Upvote 0

B

bvas

New Member

Joined

Aug 7, 2002

Messages

12

• Aug 20, 2002

• #9

Ivan's code works great if you put the code he wrote in the activate and deactivate (make enabled = true) events for the worksheet.

Delete sheet uses ID= 847. Here's a link to the article that shows other codes, such as copy:

Microsoft Knowledge Base Article - Q159466

 

Upvote 0