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.

Sub UpdateSheets()
  Dim Sh As Worksheet

    'Clear Existing List from SHEETS range
        Call ClearSheetRange

        Application.ScreenUpdating = False

    'Loop through all worksheets except for TEMPLATE and INGREDIENTS worksheets
        For Each Sh In ActiveWorkbook.Worksheets 
        If IsError(Application.Match(Sh.Name, Array("TEMPLATE", "INGREDIENTS"), 0)) Then

    'Print the Sheet names to column "E"
        Sheets("INGREDIENTS").Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Sh.Name
        End If

            Next Sh

    ' Run sorting macro
        Call SortSheetsList

        Application.ScreenUpdating = True

End Sub

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)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Static lngWs As Long
On Error Resume Next
If ThisWorkbook.Sheets.Count <> lngWs Then
    'new sheet or sheet removed
    'disable events
    Application.EnableEvents = False
    'do stuff....
    'enable events
    Application.EnableEvents = True
    'update Static
    lngWs = ThisWorkbook.Worksheets.Count
End If
End Sub

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)