Where is the Worksheet_Delete event?
 

Hi, All

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

Where is the Worksheet_Delete event?
 

ex: add a row or delete a row on the worksheet

"OKLover" bl...
Hi, All

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

Where is the Worksheet_Delete event?
 

Hi

There is no such event, sorry. An awkward workaround would be to hijack all
the Delete / Insert menus and assign your own macros to them.

HTH. Best wishes Harald


"OKLover" skrev i melding
...
ex: add a row or delete a row on the worksheet

"OKLover"
bl...
Hi, All

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

Where is the Worksheet_Delete event?
 

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)