PDA

View Full Version : Prevent deleting worksheet



marreco
03-23-2013, 02:20 PM
Hi.

1. Prevent a specific plan was not possible to exclude (Sheet1)
2. Prevent File no plan could be excluded, but that could be added further.

I tried it but failed.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sht As Worksheet: Set sht = Sh
If sht.Name = "Plan1" Then
SetDeleteOption (False)
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim sht As Worksheet: Set sht = Sh
If sht.Name = "Plan1" Then
SetDeleteOption (True)
End If
End Sub
Private Sub SetDeleteOption(ByVal AllowDelete As Boolean)
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 = AllowDelete
Next
End Sub
Thanks

SamT
03-24-2013, 02:33 AM
This is very rough, but, maybe :115:


'ThisWorkBook Code
Sub Workbook_Open()
PreserveSheet
End Sub

Sub WorkBook_BeforeClose()
PreserveSheet
End Sub

'Module Code

Function PreserveSheet()
Static SaveSht As OBject
Set SaveSht = Sheet1`

If Sheet1 is Nothing Then
Workbook.Add Sheeti
Set Sheet1 = SaveSheet
WorkBook.Save
Set SaveSheet = Nothing
End If
End Function

marreco
03-24-2013, 05:27 AM
Hi.

did not work also gave me the following message.


Copile error:
Procedure declaration does not match description of event or procedure havin the same name

sassora
03-24-2013, 03:56 PM
Try protecting the sheet instead:


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

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


Change to Workbook_activate / deactivate if needed