Results 1 to 6 of 6

Capture Add-Delete Worksheet Events

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-11-2009
    Location
    NYC,USA
    MS-Off Ver
    Excel 2007
    Posts
    135

    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.
    Attached Files Attached Files
    Last edited by max57; 03-15-2010 at 01:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1