how to prevent a worksheet from being deleted?

[muffins]

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.

 

[Mudface]

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.

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

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

 

[Ivan F Moala]

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.

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>

 

[Ivan F Moala]

Oh BTW

Reset this before closing
Just set the Enabled to True

 

[Askar]

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

 

[stevebausch]

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.

 

[muffins]

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

 

[Mudface]

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).

 

[bvas]

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