Excel VBA Before Delete Worksheet Event

[mmogul]

I am running Excel 2010. I have an application that allows the user to add worksheets so I cannot turn on Workbook Structure protection. Yet there are some worksheets in the application that I do not want the user to delete. If there was a Before Delete Worksheet event, I could check the name of the worksheet, and if required, cancel the event.

Does anyone have any thoughts for a workaround?

Thanks.

 

[SkipVought]

hi,

In the ThisWorkbook code sheet there is this event...

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

End Sub

 

[mmogul]

Thanks. Are you running Excel 2013? I don't see this in Excel 2010.

 

[SkipVought]

I believe it's been there since at least Excel '97!

Click ThisWorkbook object, select Workbook from upper left-hand drop down, select events from upper right-hand drop down.

 

[mmogul]

Maybe I'm blind but I do not see it (I attached an image of my screen). I see:

SheetBeforeDoubleClick
SheetBeforeRightClick

and those are the only SheetBefore... events I see.

I tried it anyway but it did not fire.

 

[SkipVought]

You are not blind. It seems this is a new event. Sorry.

This may not be an event that you and trap, and even if you could, it seems that there is no way to CANCEL the event. This is one of the drawbacks of the Office Application. They are not very well suited to tightly controlled custom applications. Its wide open for user interaction, meaning that structure is hard to control.

 

[mmogul]

Thanks for checking this out. So my idea to handle this is to use the SheetDeactivate and SheetActivate events. On a SheetDeactivate, I can create a temp copy of the worksheet and count the number of worksheets.

On SheetActivate, take another count. If the count has decreased, I know that the sheet was deleted. If I want to prevent that sheet from being deleted, I can copy the temp sheet back to the original name. Or I can delete the temp sheet.

Kind of awkward, but this might work.

 

[mmogul]

Nah - this approach does not work because the Copy itself creates a SheetDeactivate event which causes an endless loop.

i.e. Move to new sheet starts SheetDeactivate. The idea was to have sheetDeactivate event create a copy of the worksheet. But the act of copying the worksheet causes focus to go to the new worksheet, which in turn kicks off the SheetDeactivate event.

Is there a way to copy a worksheet and have focus remain on the same worksheet so the SheetDeactivate event is not started?

 

[PHV]

You may play with the Application.EnableEvents prperty.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

Thanks. Sounds promising.

 

[zelgar]

I think your original premise of "I have an application that allows the user to add worksheets so I cannot turn on Workbook Structure protection." is wrong. You can both protect an individual sheet and/or the workbook. You can also turn the protection on and off if you like (and it can be done in a macro). In Excel 2010, under the Review Tab is where the Protect options can be found.

 

[mmogul]

Zelgar - Thanks. I hope you can prove me wrong. But with my application, I wish to protect certain worksheets from deletion but want users to be able to add other worksheets. Without getting into a lot of extra programming (like removing menus and adding customized buttons to do all this), I don't think this can be done with standard protection.

If the workbook structure is turned on, the user cannot add or delete worksheets. Even if a worksheet is protected, a user can still right click on the tab and then delete that worksheet or change its name. Maybe I am missing something. I hope so. I think I have a solution which I hope to publish when I get out the kinks, but a standard application option would certainly be preferable.

 

[SkipVought]

Just thinking out loud here.

We use PivotTables a lot. When the user double-clicks in a PT, a new sheet is added (standard Excel feature) with the data associated. The new sheet name is "Sheet" something. All current sheets are not named "Sheet" something. When the user Deselects any sheet the Workbook_SheetDeselect event deletes the Sh object IF the sheet name begins with "Sheet".

 

[combo]

What is the purpose of worksheets you need to protect? do they have to be visible? If not, make them very hidden. Does it make sense to split worksheets into two workbooks?

combo

 

[mmogul]

Let me provide a more comprehensive explanation of my application. My client has asked me to build a standardized goal setting system for marketing and sales personnel. It sets out goals and then measures performance against the goals. The simplest workbook is comprised of an Administrative worksheet and a second worksheet that sets the goals for an individual. I'll call these the core worksheets. (There is a lot of vba code behind these sheets).

The Goal worksheet can be copied by the user (there is a button on the goal worksheet) that brings over the Goal worksheet form and its data, making it easier for the user to set the goals for another employee. The user might also want to create his own worksheet within this workbook that provides some supporting data. He also might want to delete these additional worksheets. The user is allowed to rename the sheets, except for he Administrative sheet.

All of these worksheets need to be visible.

So in summary, a user can create a workbook with a minimum of 2 worksheets or the workbook could be comprised of multiple Goal worksheets. I need to make sure that the user can delete these additional worksheets but cannot delete the Core worksheets.

It appears that in Excel 2013, there is a deleteworksheet event. With that event, I could easily prevent the Core worksheets from being deleted. But in earlier versions, it appears I need to create a workaround.

I hope that background helps.

 

[SkipVought]

Might it help to have a master workbook that is never directly opened by the user and a user workbook that gets the admin sheet and goal sheet from the master?

 

[mmogul]

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.

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:

https://mogultechnology.wordpress.com/

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:

http://datapigtechnologies.com/blog...t-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:
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.

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.