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
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
End Sub

This (silly) VBA macro looks for the sheet named “Sheet3” in the Active Workbook, and deletes it – and if the user executes that macro while another sheet is active – say, Sheet1 – the active worksheet won’t change.

In a desperate attempt, I looked into the SheetChange event from the Workbook class, but this was another miserable failure – this one catches the changes in the contents of the ranges, but doesn’t blink when the sheet is deleted.

I guess I’ll have to accept that the best I can do is through the sheet activation, but I am really puzzled by what seems to me an incomprehensible oversight – and a very annoying one.

For the Excel and VSTO gurus out there, am I missing something obvious? Is there a secret Jedi trick which will unambiguously reveal that a worksheet is gone, or that the Worksheets collection has been modified?

Comments

3/10/2010 12:12:01 AM #

Dennis Wallentin

Mathias,

AFAIK, there is no way we can trap the event deleting a worksheet. A workaround is to add a timer that with an  interval count the open worksheets and make an update when the number of worksheets is changed.

Kind regards,
Dennis

Dennis Wallentin Sweden | Reply

3/10/2010 4:40:24 PM #

mathias

Even though you are confirming my fears, thank you for the input, Dennis! I thought about implementing the solution you are describing - a regular "poll" of the worksheets - but I was really hoping I wouldn't have to... I am really puzzled as to why this event hasn't been implemented, when the equivalent exists for Workbooks - very odd.

mathias United States | Reply

3/16/2010 4:10:35 PM #

trackback

Create an Excel 2007 VSTO add-in: Excel events

Create an Excel 2007 VSTO add-in: Excel events

Clear Lines Blog | Reply

6/6/2010 10:14:06 AM #

Peter Carr

Great series of articles Mathias.
Another Excel event that is missing, is a Sheet Rename event.  This would also effect your add-in

Peter Carr Australia | Reply

6/7/2010 5:02:06 AM #

Mathias

Thanks for the feedback, Peter! And you are absolutely correct about the Sheet renaming issue, which will be a problem whenever a list of worksheet names needs to be maintained in sync with the open sheets. I remember thinking about that while working on the tree view, and I think I did refresh the names in the procedure that "re-syncs" the tree, but I am not 100% positive about that - I'll have to check!
Mathias

Mathias United States | Reply

2/13/2011 4:57:11 AM #

trackback

Create an Excel 2007 VSTO add-in: wrapping up part 1

Create an Excel 2007 VSTO add-in: wrapping up part 1

Clear Lines Blog | Reply

2/13/2011 4:57:11 AM #

trackback

Create an Excel 2007 VSTO add-in: wrapping up part 1

Create an Excel 2007 VSTO add-in: wrapping up part 1

Clear Lines Blog | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS