Thanks everyone for your help. I finally worked out a solution to preventing the deletion of worksheets in a non-protected workbook. You would use this when the workbook has some “required” worksheets (cannot be deleted) but other worksheets can be added or deleted. After I developed this approach, I found a far simpler and elegant method.
Method 1 – Complex method
Create a customized worksheet delete event
The basic approach is to first recognize if a worksheet has been deleted, basically creating a customized WorksheetDelete Event. This is done by counting the number of worksheets in the SheetDeactivate Event (storing that value in a global variable) and then performing the same count in the SheetActivate Event. If the count has decreased, then you know the user deleted a worksheet. If this worksheet is not allowed to be deleted, then it must be restored. This could be useful for special processing after a worksheet is deleted.
Code:
Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
'** iWksCountBefore was declared as a global variable
iWksCountBefore = ActiveWorkbook.Sheets.Count
Private Sub Workbook_SheetActivate(ByVal ws As Object)
iWksCountAfter = ActiveWorkbook.Sheets.Count
If iWksCountAfter < iWksCountBefore Then
'** The worksheet was deleted - do something
Else
End If
Exit Sub
ErrorHandling:
End Sub
Ok - that part was simple. The problem was what to do if the user was not allowed to delete that particular worksheet. How do you restore it?
Restoring the Worksheet
In order to restore the worksheet, some preliminary steps need to be taken.
1) Back up the worksheet in the WorksheetDeactivate Event. I found that copying the worksheet to another worksheet in the workbook ultimately created issues with Range Name. So instead I copy the worksheet to a temporary workbook.
2) Store all of the named ranges into a global array. We will need this later
3) If the “protected” worksheet was deleted, recognize that in the WorksheetActivate Event and copy back the backed up worksheet to this workbook. (Prior to restoring the worksheet, you need to turn off events (Application.EnableEvents = False) so the restoration does not kick off another Sheet Activate / Deactivate event causing an endless loop)
4) When the worksheet from the temporary workbook is copied, range names can still refer back to the temp file. In order to clean this up, you need to do 3 things:
a. Delete all range names in the workbook
b. Recreate the range names by using the stored Named Range data in the global array (see step 2)
c. Prior to restoring the worksheet, you need to turn off events (Application.EnableEvents = False) so the restoration does not kick off another Sheet Activate / Deactivate event causing an endless loop
5) Delete the temporary file
You can find this code on my blog at:
This is a lot of code, kind of clunky but it works.
Method 2 – Much more elegant with a lot less code
(credit for this idea must be given to Jan Karel Pieterse – see link:
)
In Excel 2013, there is a worksheet delete event but it does not have a cancel method. In earlier versions, there is no worksheet delete event at all. In Excel 2010 (and I assume earlier versions), the events occur in the following order for adding and deleting a worksheet:
1) Worksheet added (runs immediately before the Workbook_SheetDeactivate event)
2) Workbook_SheetDeactivate Event called
3) Worksheet Deleted (runs immediately after Workbook_SheetDeactivate but before Workbook_SheetActivate)
4) Workbook_SheetActivate Event called
Remember, we have Workbook protection off so the user can add worksheets. This occurs before the Workbook_SheetDeactivate event. But what about worksheets that a user deletes? Let’s assume a user tries to delete a required worksheet. If we can turn Workbook protection on in the Workbook_SheetDeactivate event, the Worksheet Delete cannot occur. After we have protected the sheet from being deleted, we then need to turn Worksheet Protection off.
So here is the solution.
Code:
Private Sub Workbook_SheetDeactivate (ByVal ws as object)
‘** set the condition here that determines if this particular worksheet is to
‘** be protected from deletion and run the following code if true.
‘ ……….
‘** Step 1: Turn on protection
ThisWorkbook.Protect, True
‘** Step 2: Call sub that turns off protection using the Application.OnTime method (I do not yet ‘**understand why this works and a direct sub or function call does not work)
‘**
Application.OnTime Now, “ThisWorkbook.UnprotectBook
End Sub
Public Sub UnprotectBook()
ThisWorkbook.Unprotect
End Sub
What is happening here?
Step 1 - turns on Workbook protection
Step 2 - calls the UnprotectBook sub. But before this sub is run, Excel attempts the worksheet delete. Since the workbook is now protected, the worksheet delete fails and the user gets the system message:
Workbook is protected and cannot be changed
(can anyone figure out how to replace this with a custom message?)
The SheetActivate event is never run because the worksheet deletion did not occur so focus remains on the current worksheet.
Step 3 – now the UnprotectBook sub is run and workbook protection is turned off.