PDA

View Full Version : Solved: prevent worksheet deletion.



omnibuster
05-04-2010, 01:27 PM
Hi.
If i open wbk, code does not affect immediately?

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

mdmackillop
05-04-2010, 01:43 PM
Try Private Sub Workbook_Open() instead of Private Sub Workbook_SheetActivate(ByVal Sh As Object)

omnibuster
05-04-2010, 10:27 PM
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
05-04-2010, 10:47 PM
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
05-04-2010, 11:12 PM
Big Thanks GTO.
Works good.