Event for Sheet DELETE 1

[tmktech]

Hello, folks.

I need to maintain a list of worksheets within a given workbook for various reasons (one is to use for Data Validation of a cell). I know how to loop through the sheets to build the list, but here's my problem / question.

The user can add and/or delete sheets. While the "NewSheets" event will capture additions (at which point I'll update the list), HOW DO I DETECT DELETED sheets?

Am I missing something simple?

Thanks in advance!!

TMKTECH

 

[SkipVought]

Well, lots of ways to skin this cat...

You keep a list of sheet names. On the Workbook_SheetDeactivate event, you check the list against the existing sheet names.

VOLA! Skip,
Skip@theofficeexperts.com

 

[RobBroekhuis]

Even more foolproof, use the SheetActivate event. When a sheet is deleted, another one will always be activated, so you catch the event immediately. Upon deletion, a sheet_deactivate event will also fire, but the sheet will still be part of the sheets collection.
Rob

 

[tmktech]

Thanks, Rob and Skip!

Sounds like I won't be able to avoid looping through the sheets collection (and re-building the list) each time a sheet is activated or deactivated, RIGHT ? I was just trying to keep the processing to a minimum.

TMKTECH

 

[RobBroekhuis]

You don't have to loop through each time. You can keep track of the number, and only loop through when it changes:

sub workbook_sheetactivate
if sheets.count<>iSheetCount then
...find which one got deleted here...
iSheetCount=iSheetCount-1
end if
end sub

Rob

 

[tmktech]

That'll do it, Rob.

Here's another star.