I have a Excel project where there is a workbook with “required” worksheets (users cannot delete them) but users can add / modify other sheets. So the workbook structure cannot be protected, yet I have to make sure these required worksheets are not deleted.
I am using Excel 2010, which does not have a Worksheet Delete event. This does exist in Excel 2013, but even that event does not have a Cancel method.
So I needed to develop a solution to handle this issue. Here are two methods. The first is fairly complex, but it works. But then I discovered method 2, which is much cleaner and simpler. The method 1 approach may still have some value, particularly if you need to create a customized worksheet delete event.
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.
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.
- 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.
- Store all of the named ranges into a global array. We will need this later
- 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)
- 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:
- Delete all range names in the workbook
- Recreate the range names by using the stored Named Range data in the global array (see step 2)
- 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
- Delete the temporary file
This is a lot of code, kind of clunky but it works. Go to the bottom of this entry to see the code.
Method 2 – Much more elegant with a lot less code
(credit for this idea must be given to Jan Karel Pieterse – see link: http://datapigtechnologies.com/blog/index.php/prevent-worksheet-delete-without-workbook-protection/#respond )
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:
- Worksheet added (runs immediately before the Workbook_SheetDeactivate event)
- Workbook_SheetDeactivate Event called
- Worksheet Deleted (runs immediately after Workbook_SheetDeactivate but before Workbook_SheetActivate)
- 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.
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.
A much simpler solution.
Method 1 Code
Here is the code for Method 1
Private Sub Workbook_SheetDeactivate(ByVal ws As Object) Call StoreAllNamedRanges(ActiveWorkbook) Call CopyWorksheetAsTemp(ws) iWksCountBefore = ActiveWorkbook.Sheets.Count Application.EnableEvents = True End Sub Private Sub xWorkbook_SheetActivate(ByVal ws As Object) Dim xlWB_Scorecard As Workbook Dim xlWB_Temp As Workbook Dim xlWS_Temp As Worksheet Dim sCurrentSheetName As String '** The parameter bDeleteWorksheetProtection" determines if this code will run '** It is set to false while it is developed. This is used as a global variable '** since the range name is lost if the admin worksheet is deleted Set xlWB_Scorecard = ActiveWorkbook 'MsgBox "activate" iWksCountAfter = ActiveWorkbook.Sheets.Count '** If the worksheet count decreased, then a worksheet was deleted If iWksCountAfter < iWksCountBefore Then Set xlWB_Temp = Workbooks.Open("Temp1") Set xlWS_Temp = xlWB_Temp.Worksheets(1) xlWB_Scorecard.Activate '** This assumes you want to prevent this worksheet deletion - you need code here to determine that '** Before copy back the Admin worksheet - delete all the range names / copy the worksheet / then add '** the range names back Call ClearAllNamedRanges(ThisWorkbook) Application.EnableEvents = False xlWS_Temp.Copy ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Application.EnableEvents = True '** The range names need to be cleared again after copying in the Temp worksheet Call ClearAllNamedRanges(ThisWorkbook) Call LoadAllNamedRanges(ThisWorkbook) xlWB_Temp.Close savechanges:=False Else End If If IsFile("Temp1.xlsm") Then Kill "Temp1.xlsm" Else End If Application.EnableEvents = True End Sub '************* Public Function StoreAllNamedRanges(xlWB As Workbook) As String() '** This function stores all named ranges in a workbook into an array '** The array is two dimensional - '** Dimension 1 - the identifier of the named range '** Dimension 2 - the attribute of the array where: '** 1=name 2= refersto 3= Scope (wb or ws) 4= comment '************************************************************************ Dim iRangeCount As Integer Dim i As Integer Dim rngName As Name 'Set wb = ActiveWorkbook iRangeCount = xlWB.Names.Count ReDim sAllRanges(1 To iRangeCount, 1 To 4) For Each rngName In xlWB.Names i = i + 1 sAllRanges(i, 1) = rngName.Name sAllRanges(i, 2) = rngName.RefersTo If TypeOf rngName.Parent Is Workbook Then sAllRanges(i, 3) = "wb" Else sAllRanges(i, 3) = "ws" End If sAllRanges(i, 4) = rngName.Comment Next rngName End Function '*********** Public Sub CopyWorksheetAsTemp(xlWSSource As Worksheet) '** This sub copies the current scorecard worksheet to a new worksheet '** and retains the existing data 'enable events are turned off during this process Dim sMsg As String Dim iRet As Integer Application.EnableEvents = False xlWSSource.Copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs "Temp1", FileFormat:=52 ActiveWorkbook.Close Application.DisplayAlerts = True End Sub Public Function ClearAllNamedRanges(xlWB As Workbook) As String() '** This function clears all named ranges in a workbook '************************************************************************ Dim rngName As Name For Each rngName In xlWB.Names rngName.Delete Next rngName End Function '******** Public Function LoadAllNamedRanges(xlWB As Workbook) As String() '** This function load all named ranges from an array into the specified workbook '** The array is two dimensional - '** Dimension 1 - the identifier of the named range '** Dimension 2 - the attribute of the array where: '** 1=name 2= refersto 3= Scope (wb or ws) 4= comment '************************************************************************ Dim iRangeCount As Integer Dim i As Integer Dim xlWS As Worksheet Dim iPosition As Integer Dim sWorksheet As String Set xlWS = xlWB.ActiveSheet 'Set wb = ActiveWorkbook For i = 1 To UBound(sAllRanges, 1) If sAllRanges(i, 3) = "wb" Then xlWB.Names.Add Name:=sAllRanges(i, 1), RefersTo:=sAllRanges(i, 2) Else 'Determine the worksheet for the range iPosition = InStr(1, sAllRanges(i, 2), "!") sWorksheet = Mid(sAllRanges(i, 2), 2, iPosition - 2) '** Replace the apostrophe found in worksheets that have spaces with a null space sWorksheet = Replace(sWorksheet, "'", "") Set xlWS = xlWB.Sheets(sWorksheet) xlWS.Names.Add Name:=sAllRanges(i, 1), RefersTo:=sAllRanges(i, 2) End If Next i End Function