Capture Add-Delete Worksheet Events

I would like some help capturing, and incorporating, both the “Add Sheet” and “Delete Sheet” events for a workbook.

I have a Dynamic Range on a sheet in which this Workbook’s sheets names, minus any I don’t want listed, are placed by a macro.

What I need help with, is if the User Adds a sheet or Deletes a sheet, the change will trigger the macro I have in place.

The Macro called “UpdateSheets” works as follows:
1. It first clears the “SHEETS” dynamic range on the “INGREDIENTS” sheet.
2. The sheet names are then relisted, taking into account any that have been added or deleted.
3. The list is then sorted.

Please Login or Register  to view this content.

I’ve seen one example in which a Delete Class module was written, but I was unsure how to adapt that code into something I could use here.

One thing to remember is that the INGREDIENTS sheet will not be the active sheet when this event is triggered, so I wish to avoid a “Runtime Error 1004” Method of Range Object_Global Failed.
(I have rotten luck fixing these.)

There is a workbook attached for illustration purposes.

Thanks for your time.

Hi,

There's a workbook 'NewSheet' event which you could use to call the UpdateSheets macro, but no similar DeleteSheet event, so perhaps it may be better to use the Workbook BeforeSave event and call the macro from there.

HTH

Deleting / Inserting a sheet should invoke the Sheet Activate event.

In theory you could on that basis use that Workbook level event to store a Static variable keeping track of the number of sheets in that workbook.
As and when the number changes invoke the other routine.

(the Static will be lost between "sessions" so the code will also fire the first time the workbook calculates)

Please Login or Register  to view this content.

the above would reside in ThisWorkbook in VBE

(the above would need to usurp the NewSheet event - not ideal but as outlined in should cater for both insertion & deletion simultaneously)