Grüezi OKLover

OKLover schrieb am 02.07.2005

Is anyone knows how to trap the (after/before) delete or add event on the
worksheet?

As Harald mentioned - there is no such event.
....but whenever a sheet is added or deleted, Workbook_SheetActivate fires.

So in a module set the following line:

Public lngSheets As Long


and in 'This Workbook' the following two event-codes:

Private Sub Workbook_Open()
lngSheets = Sheets.Count
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sheets.Count
Case Is lngSheets
MsgBox "there was a sheet added"
'add whatever you like
Case Is < lngSheets
MsgBox "there was a sheet deleted"
'add whatever you like
End Select
lngSheets = Sheets.Count
End Sub

Save, close and reopen the workbook and whenever a sheet is added or
deleted you'll get a message.


Mit freundlichen Grüssen
Thomas Ramel

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)