Worksheet_Delete event?

[Cool Sport]

Hi there,

Is there such an event like Worksheet_Delete or Chart_Delete? If there
isnt any, then how do we know when a worksheet or chart sheet is deleted
by user.

Regards,

 

[Tim Williams]

AFAIK there are no events like that. If it's essential for you to
catch these events then you could instead protect the workbook and
provide the user with a button to delete a sheet or chart.

Tim

 

[Guest]

There's a workaround of course. if you record the worksheet that was
deactivated and test for this in the sheet activate event, you can see which
sheet was removed

in ThisWorkbook code page:

Option Explicit
Private WSName As String
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim ok As Boolean
For Each ws In Worksheets
If ws.Name = WSName Then
ok = True
Exit For
End If
Next
If Not ok Then
MsgBox WSName & " has been deleted"
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
WSName = Sh.Name
End Sub

a better alternative would be toi keep a list of worksheets, graph sheets
etc, then on a deactivate event, simple look up whats missing.

 

[Cool Sport]

Many thanks for your responses!

 

[tkt_tang]

1. Make use of the Deactivate event.
2. You may have already kept a current list of Worksheets ; in the
event of Deactivating a worksheet and then it disappears from the list
....... then you know that it's deleted.
3. Look at the archive of this site. It tells plenty ; a treasure trove
indeed.
4. Regards.