does an event get triggered when you delete a sheet?

[pliant]

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...

 

[TommyGun]

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?

 

[bobm]

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?

 

[pliant]

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.

 

[Andrew Poulsom]

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

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.

 

[pliant]

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

 

[pliant]

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

 

[Andrew Poulsom]

This worked for me:

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.

 

[pliant]

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

 

[Andrew Poulsom]

Put

 immediately before your code and

immediately after it. BBCode must be enabled.