does an event get triggered when you delete a sheet?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: does an event get triggered when you delete a sheet?

  1. #1
    Board Regular
    Join Date
    Jan 2003
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    is there any event triggered when you delete a sheet?

    when a user deletes a sheet i would also like to delete that sheet from a list of sheets that i have on another sheet...

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I do not know of an event that is triggered when a sheet is deleted.

    There may be another way to accomplish what you are looking for though. Can you explain why you need to delete the sheet name from a list of names?

  3. #3
    Board Regular
    Join Date
    Jan 2003
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How about code in the Sheet_Activate event of the sheet with the list that will iterate through the sheets in the workbook and generate a fresh list?

  4. #4
    Board Regular
    Join Date
    Jan 2003
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have a list of items that populates a combo box and a separate sheet for each item.

    if the user deletes a sheet i would like that item removed from the combo box as well so the user cannot select it.

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put this code in the ThisWorkbook module in a blank workbook with 2 sheets:

    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        MsgBox Sh.Name & " activated"
    End Sub
    Then delete a sheet. The event is triggered.

    You can replace the MsgBox statement with the code to update your list of sheets.

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok thanks...but how do you iterate throught the sheets in the workbook??

  7. #7
    Board Regular
    Join Date
    Jan 2003
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I've figured it out. This seems to work:

    'get the name of the sheet being deactivated
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    sheetName = Sh.Name
    End Sub

    'check if it is still there when the next sheet is activated
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim sheetCount As Integer
    Dim found As Boolean

    sheetCount = Sheets.Count

    For i = 1 To sheetCount
    If Sheets(i).Name = sheetName Then
    MsgBox ("sheet found")
    found = True
    End If
    Next

    If Not found Then
    MsgBox ("sheet NOT found!")
    'delete from list
    End If

    End Sub

    [ This Message was edited by: pliant on 2003-01-17 13:17 ]

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This worked for me:

    Code:
    Sub Test()
        Dim Sh As Worksheet
        Dim Rng As Range
        Dim x As Integer
        Dim WS As Worksheet
    '   *** Sheet containing list of worksheets - change to suit ***
        Set Sh = Worksheets("Sheet1")
    '   *** Range containing worksheet names - change to suit ***
        Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlDown).Row)
        For x = Rng.Rows.Count To 1 Step -1
            On Error Resume Next
            Set WS = Worksheets(Rng.Cells(x, 1).Text)
            If Err <> 0 Then
                Rng.Cells(x, 1).EntireRow.Delete
            End If
        Next x
    End Sub
    It assumes that your list of worksheets starts in A2 on Sheet1 - change to suit.

  9. #9
    Board Regular
    Join Date
    Jan 2003
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how do you get your code to appear between the lines like that?

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Put
    Code:
     immediately before your code and
    immediately after it. BBCode must be enabled.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com