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
‘Name the copy to the original name
ThisWorkbook.ActiveSheet.Name = MyName
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:
Then for every worksheet, right-click, select View Code, and then paste this:
Private Sub Worksheet_Deactivate()
ThisWorkbook.Protect , True
Application.OnTime Now, “UnprotectBook”
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.