While working on my VSTO Excel add-in tutorial, I came across the following issue: I need to know whether a worksheet has been deleted. The reason I care is that when it happens, I need to update the display of the worksheets that are currently open, and remove it from there.

I was very surprised to find out that there seems to be no event for this. The Application object, which represents the Excel application, has a WorkbookBeforeClose event; the Workbook object has an event BeforeClose, triggered when the Workbook is being closed. So naturally, I expected to find something equivalent for the Worksheet object, at either the Application, Workbook, Sheets, Worksheets, or Worksheet level – no such luck.

I looked around on the web, and from what I can tell, there is no native event for this, and I came across multiple posts advocating to handle this through Worksheet.Activate and/or Worksheet.Deactivate. I see how this catches the obvious use case, namely, the user selects the sheet and deletes it – which causes the sheet to be activated, and then another worksheet to be activated once the deletion is performed. Unfortunately, this doesn’t catch all the cases: as far as I can tell, it is perfectly possible to delete a worksheet without ever changing which sheet is active. To prove the point, create a workbook, and add the following macro:

Public Sub DeleteSheet3()
    Application.DisplayAlerts = False
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
End Sub

More...

Just plugging the next Bay.Net Education Day (which I am organizing), where Mark Michaelis will

start with a blank slate, the Visual Studio Wizard, and proceed to create a series of assemblies that comprise .NET 3.0/.NET 3.5 sample application.  The result will be a whirl wind introduction to technologies such as Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF), Ajax, SharePoint, Visual Studio Unit Testing, LINQ and much more.  Attend this all day session to catch up with where Microsoft .NET technology is today and gain insight into where it is going tomorrow.

Should be a great event - it will take place Saturday September 6, at Foothill College; you can register here.