Prevent Worksheet Delete without Workbook Protection

My friend Tim sent me a message yesterday asking:

“I would like to allow people to insert and re-order worksheets but not delete any. Is that possible?”

Searching all the forums, I found that this question comes up with some frequency. There seems to be a common need to prevent the deletion of a worksheet, but still give users the ability to change the structure of the workbook. Excel does have a Workbook Protection feature, but that feature doesn’t give you many options when you protect a workbook. That is to say, you can’t specify that you want to prevent one action, but not other actions. So you’re stuck with either protecting the workbook structure or not.

.

The answer will have to come from VBA.

In my search, I discovered that Excel

2013 added a new Worksheet Event called BeforeDelete.


.

Wow. I hadn’t noticed this event before.

Of course, the Microsoft help files are as useful as ever (sarcastic tone).

.

Judging by the utter lack of examples on how other folks are using this, I’m assuming this new addition to the Worksheet events has gone largely unnoticed by many of us.

.

Apparently, this event triggers when you attempt to delete any worksheet in your workbook. Unfortunately, this event does not come with a Cancel method. Meaning that when a user deletes a worksheet, this event triggers, but then Excel goes ahead and deletes the worksheet anyway. It seems that the purpose of this event is to do some action before the worksheet is delete – not give the developer an opportunity to cancel the delete. So with this event, you can do things like: log the time the worksheet was deleted, save the workbook before deleting the worksheet, send an email before worksheet is deleted, or anything else you can think of.

.

I decided to solve Tim’s problem with this small bit of code in the Worksheet_BeforeDelete event. This code simply renames the worksheet, then creates a copy with the original name. So before the worksheet is deleted, you essentially create a copy of it. No matter how many times the user tries to delete the worksheet, it will always be there.

Private Sub Worksheet_BeforeDelete()

Dim MyName As String

‘Capture the original worksheet name
MyName = ThisWorkbook.ActiveSheet.Name

‘Rename the worksheet
ThisWorkbook.ActiveSheet.Name = Left(MyName, 30) + “#”

‘Create a copy of the worksheet
ThisWorkbook.ActiveSheet.Copy _
After:=Sheets(ThisWorkbook.ActiveSheet.Index)

‘Name the copy to the original name
ThisWorkbook.ActiveSheet.Name = MyName

End Sub

.

If you have Excel 2013, you can right-click on the worksheet you want protected, then select the View Code option. Then simply paste this code into the VBE. Note that you’ll have to do this for every worksheet you don’t want deleted.

.

For those of you with Excel 2010 or prior versions, you’re basically out of luck. You don’t have the BeforeDelete event available to you. In those version, you’d have to employ messy tricks to hide the Delete Commands from the interface. These require VBA and even some RibbonX customization that I’d rather not get into.

Update: Jan Karel Pieterse, observing that my technique will zap any formula pointing to the deleted worksheet, has provided an excellent solution that is far superior to the one I proposed here.

In a normal module, paste this code:

Sub UnprotectBook()
ThisWorkbook.Unprotect
End Sub

Then for every worksheet, right-click, select View Code, and then paste this:

Private Sub Worksheet_Deactivate()
ThisWorkbook.Protect , True
Application.OnTime Now, “UnprotectBook”
End Sub

And this works in any version of Excel! Thanks Jan Karel!

 

Feel free to comment and tell us if and how you’re using the BeforeDelete event. Like I said, I’ve just now noticed it. I’d like to know how anyone else is using it.

19 thoughts on “Prevent Worksheet Delete without Workbook Protection

  1. Jan Karel Pieterse

    What about doing this:

    ‘IN every sheet module:

    Private Sub Worksheet_Deactivate()
    ThisWorkbook.Protect , True
    Application.OnTime Now, “UnprotectBook”
    End Sub

    In a normal module:
    Sub UnprotectBook()
    ThisWorkbook.Unprotect
    End Sub

    Works in any Excel version!

  2. datapig

    Jan Karel: Damn you’re good. Thanks for the brilliant solution! I’ve updated the post to share it.

    Have you used the BeforeDelete event in any of your work yet?

  3. Doug Glancy

    Very nice Jan Karel. Of course you can also just put it in the ThisWorkbook’s Workbook_SheetDeactivate event.

    I agree about the Cancel event. It would be very handy!

  4. Andy Pope

    Another option for 2007 onwards is to disable the Delete Sheet commands using ribbon xml.

    Although for keyboard jockeys you would need to override ALT+EL

  5. Mark Mogul

    Hi Jan,

    Great solution. I’m trying to get a complete understanding how this works – particularly the Application.OnTime function. I am using Excel 2010. From what I can tell, the events occur as follows:

    • Worksheet added (not a controllable event)
    • Workbook_SheetDeactivate Event called
    • Worksheet Deleted (not a controllable event)
    • Workbook_SheetActivate Event called

    So it looks like the code:
    Application.Ontime Now, “ThisWorkbook.UnprotectBook”

    ends the SheetDeactivate event, which then starts the Worksheet Delete process before calling the Sub UnprotectBook (the delete fails because it is now protected). Then the Sub UnprotectBook is called which turns protection off.

    So my questions are:
    Why does this work using Application.Ontime and not work simply calling the sub?

    Is the any way to trap the error message “Workbook is protected and cannot be changed” with a customized message?

    Thanks – and again, very creative solution.

  6. Ryan Kok

    Jan this is brilliant. I’ve been trying to figure out a way to do this in excel 2010 for weeks now. Thanks so much.

  7. Ryan Kok

    Can anyone identify a reason for why Jan’s code would cause Excel 2010 to crash when trying to save, only after I’ve attempted to delete a sheet and gotten the “workbook is protected” message?

    The process works fine when I ran it on a test workbook. But I put this into a very large 26-tab sheet I’m using and it’s crashing on Save. Again, it works fine at preventing deletion, and I can save to my heart’s content, but once I’ve tried to delete a sheet, Excel 2010 crashes upon save.

  8. Will Terpening`

    I can’t seem to get this to work in 2010. I keep getting a variable not defined error for “UnprotectBook” even though the routine is a public sub in a regular VBA module. Any ideas? Thanks.

  9. Will Terpening`

    I still have not been able to figure out my problem with 2010 but I have discovered a work around.
    Place the following statement into the ThisWorkbook Workbook_SheetDeactivate event:
    ThisWorkbook.Protect , True
    Then place the following code in Thisworkbook Workbook_SheetActivate event:
    ThisWorkbook.Unprotect

    This has the additional benefit that you you can use the sh object in the deactivate event to protect only certain worksheets using the sh.name or better yet the sh.codename.

  10. Wayne

    Great Just learning VBA and the private sub beforedelete works great just what i required.
    However My question is
    How can I now allow an Authorized Person to delete a sheet without having to go into vba and commenting out the routine

    Hope you can Help

  11. Steven

    Hi

    I’m new to all this…

    I’m using excel 2010 and when I use Jan’s suggested code the following error occurs every time I test it or try to select another tab:

    run-time error ‘1004’
    Method ‘OnTime’ of object’_Application Failed

    What am I doing wrong or is there a way to fix it?

    Thanks

  12. JC

    I really appreciate these ideas. I am new to using macros. I am using Excel 2010, and I have used this code to prevent sheet deletion:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ThisWorkbook.Unprotect
    End Sub

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ThisWorkbook.Protect , True
    End Sub

    However, I have found an unfortunate problem with it. If I try to copy data from one sheet to another, the clipboard is emptied when I switch sheets. If I comment this code out, copying works fine. How can I keep this code to prevent other users from deleting sheets, but still allow copy/paste actions between sheets?

    Thanks!

  13. Alicia

    I am trying to use Jan’s solution in Excel 2013 (it has to work in earlier versions of Excel too).

    In Modules > Module 1, I have this:

    Sub UnprotectBook()
    ThisWorkbook.Unprotect
    End Sub

    In the Worksheet code, I have this:

    Private Sub Worksheet_Deactivate()
    ThisWorkbook.Protect Structure:=True, Windows:=False
    Application.OnTime Now, “UnprotectBook”
    End Sub

    When I visit the sheet and then leave it, I get a runtime 1004 error on the “Application.OnTime” line.

    I’m new at this and I really don’t know why this doesn’t work. Any help would be greatly appreciated! Thanks in advance.

  14. Carlin

    @Alicia

    The run-time error is due to a missing module reference in the OnTime method. Excel is complaining because it doesn’t know where to look for the UnprotectBook() method. To fix, include the name of the module that contains this method.

    For example: Application.OnTime Now, “Module1.UnprotectBook”

  15. ChicagoCPA

    I just wrote the following to address the issue and it seems to work very well:

    Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    Dim oSheet As Worksheet
    Set oSheet = ActiveSheet
    If oSheet.Name = Sheets(1).Name Or oSheet.Name = Sheets(2).Name Or oSheet.Name = Sheets(3).Name Or oSheet.Name = Sheets(4).Name Then

    Dim sName As String, oFSO As New FileSystemObject
    Let sName = ThisWorkbook.Path & “\TEMPORARYSAVE_” & ThisWorkbook.Name

    MsgBox “You are not allowed to delete a Template Tab ” & vbNewLine & vbNewLine & “Since I have no option to cancel this deletion request, I am going to: ” & vbNewLine & “1) Save this spreadsheet with a TEMPORARY new filename, ” & vbNewLine & “2) let Excel delete this template sheet, and then ” & vbNewLine & “3) close it without saving that change” & vbNewLine & vbNewLine & “This temporary filename is going to be: ” & sName & vbNewLine & vbNewLine & “If you want to a new template, then create your new template, move the new template to the appropriate location (see the Instructions tab) and then move the old template to the right, amongst your visible worksheets and THEN delete the old template.”

    On Error Resume Next
    Application.DisplayAlerts = False
    If oFSO.FileExists(sName) Then oFSO.DeleteFile sName

    ThisWorkbook.SaveAs sName
    ThisWorkbook.Close False
    End If
    End Sub

    You will get an error message as the code closes the spreadsheet before it is finished, but at least my hidden templates cannot be deleted, no matter what my users do and their copies can.

Leave a Reply

Your email address will not be published. Required fields are marked *