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
