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

 

[VBA Noob]

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

 

[Allanpsaila]

Almost there!! Public Macro problem

Thanks for the quick reply. One issue though it is having a problem finding the Macro for the prventshtdelete()

I put this in as a public macro under the general worksheet section? is this correct (I tried to make another one private but it seems to always want to find Preventshtdelete() function?

exact error is:

'The Macro "(filename)'!preventshtDelete' cannot be found"

Any Ideas? At least this will prevent them from deleting the sheets that I need kept in the workbook but it would be nice to run code against it (I want to Hide the sheet xlVeryHidden then return them to sheet Main)

Thanks,
Allan Psaila

 

[Allanpsaila]

Not sure exactly what happend but now even in a new sheet if I try to delete a sheet it opens up the Sheet with the delete macro and returns the same error?

Is there a way I can turn this feature off?? I have now resorted to writing a delete current sheet macro that I have to copy to the last 2 workbooks I am working on!!! Please help!

Allan

 

[Allanpsaila]

still not working but found the reason why still need help!

So the problem is with the WorkSheet_Deactivate code not running. If I run the code manually everything works fine. but for some reason even if I put in a msgbox("Test") in the activate or deactivate code it does not display anything??

Any ideas as to why. This solves my problem but it is not working correctly. I will test this on another computer to take my machine out of the possible issue list.

Thanks,
Allan Psaila

 

[Allanpsaila]

Ok my issue is now solved seems I had a Enableevents = False in the WORKBOOK_OPEN so this was limiting the functionality of the above code.

Thanks,
Allan Psaila

 

[klausnrooster]

I used the above code in one file on a network folder. Code was put in sheet module. Now for me and at least one other user (on a different machine) any time we try to delete a sheet on any excel file whatsover, the above code is invoked and the file that contains it (heretofore unopened) is opened. I've seen some odd behavior with excel, but this one takes the cake. It gets better: I commented out the code and saved the file, but the behavior persists! I noticed this weeks ago but since I practically live in the VBE, have been typing activesheet.delete in immediate pane when I need to. I just found out another excel user is affected, which means many others may be as well. How to stop this? For my education, how did this behavior ever occur? Thanks, Jay

 

[freddy21]

you can reset with this code:

[FONT=Arial][SIZE=2][COLOR=#000000]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[/COLOR][/SIZE][/FONT][B][FONT=Arial][SIZE=2][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/B]

 

[klausnrooster]

Thanks Freddy. I'll try that monday. I deleted the file with that code, and any "saved as" copies I could find. Still the behaviour persists. Command is and always was available in the edit menu - but it just doesn't work. Can't wait to try your solution.

 

[klausnrooster]

(Slaps forehead)