Solved: prevent worksheet deletion.

[omnibuster]

Hi.
If i open wbk, code does not affect immediately?

[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet

Application.ScreenUpdating = False

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
Application.ScreenUpdating = True
End Sub

Sub Outdel()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=847)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub Indel()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=847)
Ctrl.Enabled = True
Next Ctrl
End Sub
[/VBA]

[mdmackillop]

Try Private Sub Workbook_Open() instead of Private Sub Workbook_SheetActivate(ByVal Sh As Object)

[omnibuster]

Thanks mdmackillop.
Imho wbk open event triggered once (when wbk open) and depend of active Sheetname code enable or disabled Del option for all sheets.

[GTO]

Hi there,

Not super well tested, but an alternative would be to use the workbook activate and deactivate events. This way we haven't disabled the control for other workbooks that the user may have open in the same instance. Also, as you currently have it, you could close the wb (or even quit the app) with the control disabled.

Again, not super well tested (running late to work...), but try:

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

Hope that helps,

Mark

[omnibuster]

Big Thanks GTO.
Works good.