Prevent user from delete worksheet

method 1:

Private Sub Worksheet_Activate()

Dim Ctrl As Office.CommandBarControl

For Each Ctrl In Application.CommandBars.FindControls(ID:=847)

Ctrl.Enabled = False

Next Ctrl

End Sub

Private Sub Worksheet_Deactivate()

Dim Ctrl As Office.CommandBarControl

For Each Ctrl In Application.CommandBars.FindControls(ID:=847)

Ctrl.Enabled = True

Next Ctrl

End Sub

----------------------------------------------------------------------------------------------------------------

Method 2:

Option Explicit

Private Sub Workbook_Activate()

Call Workbook_SheetActivate(ActiveSheet)

End Sub

Private Sub Workbook_Deactivate()

Call Indel

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ActiveSheet.Name = ("Data") _

Or ActiveSheet.Name = ("Query") _

Or ActiveSheet.Name = ("Sheet1") Then

Outdel

Else

Indel

End If

Next ws

End Sub

Private Sub Outdel()

Dim Ctrl As Office.CommandBarControl

For Each Ctrl In Application.CommandBars.FindControls(ID:=847)

Ctrl.Enabled = False

Next Ctrl

End Sub

Private Sub Indel()

Dim Ctrl As Office.CommandBarControl

For Each Ctrl In Application.CommandBars.FindControls(ID:=847)

Ctrl.Enabled = True

Next Ctrl

End Sub

----------------------------------------------------------------------------------------------------------------

Method 3:

Private Sub Worksheet_Activate()

Dim CB As CommandBar

Dim Ctrl As CommandBarControl

For Each CB In Application.CommandBars

Set Ctrl = CB.FindControl(ID:=847, recursive:=True)

If Not Ctrl Is Nothing Then

Ctrl.OnAction = "RefuseToDelete"

Ctrl.State = msoButtonUp

End If

Next

End Sub

Private Sub Worksheet_Deactivate()

Dim CB As CommandBar

Dim Ctrl As CommandBarControl

For Each CB In Application.CommandBars

Set Ctrl = CB.FindControl(ID:=847, recursive:=True)

If Not Ctrl Is Nothing Then Ctrl.OnAction = ""

Next

End Sub

Private Sub ResetDelete()

Dim CB As CommandBar

Dim Ctrl As CommandBarControl

For Each CB In Application.CommandBars

Set Ctrl = CB.FindControl(ID:=847, recursive:=True)

If Not Ctrl Is Nothing Then

Ctrl.Reset

Next CB

End Sub

'put in standard module

Public Sub RefuseToDelete()

MsgBox "This worksheet should not be deleted!", _

Buttons:=vbExclamation, _

Title:="Cannot Deleteworksheet!"

End Sub