MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Event for Sheet_Delete ?


Posted by Juan Pablo on September 07, 2001 1:33 PM

Is there any event or something like that, that notifies me when a user tries to delete any sheet ?

What i want to do is that if the user tries to delete SheetA1 or SheetB1 it shows a confirmation and then DELETE BOTH ! i need them together, so i want to delete them together... any ideas ?

Thanks

Juan Pablo


Posted by Ivan F Moala on September 07, 2001 4:07 PM

Try this;
Change sheets to delete names as necc


Option Explicit

Dim shName As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Avail

On Error Resume Next
Avail = Sheets(shName).Range("A1")
If Err Then
If shName = "SheetA1" Or shName = "SheetB1" Then
Application.DisplayAlerts = False
Sheets("SheetB1").Delete
Sheets("SheetA1").Delete
Application.DisplayAlerts = True
End If
End If

On Error GoTo 0

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
shName = Sh.Name
End Sub

'This works on the fact that the Workbook_SheetDeactivate event is triggered before
'the SheetActivate event and stores the Sheet name in variable shName.
'The sheetActivate event runs a little routine that
'tries to get a value from the lastsheet, which is
'shName (from the DeactivateEvent), the on Error Resume next is put there so that if the sheet no longer
'exists it generates an error object which is evaluated.


'Ivan

Posted by Juan Pablo on September 10, 2001 6:42 AM

Thanks Ivan,

Works great.

: Is there any event or something like that, that notifies me when a user tries to delete any sheet ? : What i want to do is that if the user tries to delete SheetA1 or SheetB1 it shows a confirmation and then DELETE BOTH ! i need them together, so i want to delete them together... any ideas ? : Thanks : Juan Pablo