Prevent Delete / Allow Deletion of Sheets in Excel VBA

[Allanpsaila]

Hello, I have a number of sheets in a work book and I want to run code when the user tries to delete one of the sheets. But if the sheet they want to delete is from a pivot-table drill-through then they should be allowed to delete it.

I know there is no 'On Delete' function (otherwise this would be easy).

I know the sheet names of course for the sheets that need to be in the workbook but how can I prevent them from deleting the sheets without having to protect the entire workbook?

I have only seen solutions where there are counts of worksheets but this will only notify if there was a delete (and will not work if there is a page added).

Any help would be greatly appreciated.

Allan Psaila

 

[saliman]

Hi,

Maybe this code will help

Enter this in a normal module

Public Sub PreventShtDelete()

MsgBox "This sheet should not be deleted!", _
Buttons:=vbExclamation, _
Title:="Cannot Delete !"
End Sub

then in the sheet module (right click sheet tab > Select view code) paste in this

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 = "PreventShtDelete"
      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

HTH

VBA Noob

Old forum - I know, but this is exactly what I need, however, this is not working for Excel 2010/2013. Would the vba need adapting to work with Excel 2010 and above?

 

[amisagi]

Hi - has anyone got a solution on this for Excel 2013?

I am trying to prevent a specific sheet in a workbook from being deleted/ moved / copied by user.
(have already put in a workaround for renaming)
Cant use "protect workbook" as users need to be able to add/ modify/ delete all other sheets on workbook.

The codes on this thread dont work on 2013, and my knowledge is quite limited (<2 months on VBA...) to be able to fix it.

So calling out to forum members ... please help!!