Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 11. March 2010 12:15

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
    Application.DisplayAlerts = True
End Sub



Comment RSS