Worksheet-DELETE event???

[Prosenjit Banerjee]

I have to run some code whenever the user deletes a worksheet. i.e. I want to trap an event (if any) of deleting the sheet. But, I could not found any such event. What should I do? Please help me.

 

[Ivan F Moala]

On 2002-04-17 05:36, Prosenjit Banerjee wrote:
I have to run some code whenever the user deletes a worksheet. i.e. I want to trap an event (if any) of deleting the sheet. But, I could not found any such event. What should I do? Please help me.

These is no such event for this...but you could
try this work around;

<pre/>

Dim shName As String
Dim Avail

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Avail = Sheets(shName).Range("A1")
If Err Then
MsgBox shName & " has been Deleted ...Put your routine here to run?"
End If
On Error GoTo 0
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Application.DisplayAlerts = False
shName = Sh.Name
'Application.DisplayAlerts = True
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.
'THIS IS WHERE you can put your routine you want
'to run when a sheet is deleted.


</pre>

 

[Colo]

Hi.
If you do not need the name of deleted sheet, pls copy this into a ThisWorkbook module
and try this.<pre>
Dim cntSh As Integer

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

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sheets.Count< cntSh Then Call test(cntSh - Sheets.Count)
cntSh = Sheets.Count
End Sub

Private Sub test(cntShNow As Integer)
If cntShNow = 1 Then
MsgBox cntShNow & " sheet has been Deleted"
Else
MsgBox cntShNow & " sheets has been Deleted"
End If
End Sub</pre>
This message was edited by Colo on 2002-04-17 23:16

 

[saavedrah]

Hello,

What about before deleting the worksheet ?

Thanks

 

[RoryA]

I think you would have to intercept all possible ways of deleting the sheet. Can I ask what the purpose is?

 

[saavedrah]

Hello,

I have a workbook with let's say two spreadsheets, one will allow deletion and the other one no. So if the user chooses the tab and right click on it I do not want to show the DELETE option for the spreadsheet.

In the worst case scenario, the delete option is there but somehow the "DELETE event" is risen and the application validates which sheet is deletable and which not.

I tried to "protect" the spreadsheet, but then I can not insert or delete rows/columns in that spreadsheet.

Thanks.

 

[Jaafar Tribak]

Hello,

I have a workbook with let's say two spreadsheets, one will allow deletion and the other one no. So if the user chooses the tab and right click on it I do not want to show the DELETE option for the spreadsheet.

In the worst case scenario, the delete option is there but somehow the "DELETE event" is risen and the application validates which sheet is deletable and which not.

I tried to "protect" the spreadsheet, but then I can not insert or delete rows/columns in that spreadsheet.

Thanks.

If you have Excel 2000 or later then you can prevent the deletion of all the worksheets or a specific worksheet as follows :

This will prevent the deletion of Sheets("A") - Place the code in ThisWorkbook module :

Private WithEvents cmbbtnEvents As CommandBarButton
Private Const TargetSheet As String = "A" '//change this sheet as required.
 
Private Sub cmbbtnEvents_Click _
(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
 
    If Ctrl.ID = 847 Then
 
        If ActiveSheet Is Sheets(TargetSheet) Then
 
            CancelDefault = True
            MsgBox "Can't delete sheet " & Sheets(TargetSheet).Name
 
        End If
 
    End If
 
End Sub
 
Private Sub Workbook_Open()
 
  Set cmbbtnEvents = Application.CommandBars.FindControl(ID:=847)
 
End Sub

 

[Phytoplasm]

Hi,

I've found this post after searching a method for run a macro after delete a sheet. So, I put my macro in replacement of the "Msgbox" or "Call macro" (without use the sub test in this case).
In both case excel enter in a loop and I get after one minute a stack runtime error 28; howewer I see that if I terminate it with ESC or after the error message, this macro work well because my macro is started.
I don't know if I've to write and where the code "Application.EnableEvents = False"; some tentatives are failed: solve the runtime error but block my macro too.
Naturally it's all Greek to me...

Dim cntSh As Integer
Private Sub Workbook_Open()
   cntSh = Sheets.Count
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sheets.Count < cntSh Then Call test(cntSh - Sheets.Count)
    cntSh = Sheets.Count
End Sub

Private Sub test(cntShNow As Integer)
    If cntShNow = 1 Then
        MsgBox cntShNow & " sheet has been Deleted"
    Else
        MsgBox cntShNow & " sheets has been Deleted"
    End If
End Sub

 

[johnywhy]

Hello,

What about before deleting the worksheet ?

Thanks

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
      ' prep for accidental deletion of Special Sheet
      If IsSpecialSheet(oSht) Then
            ' save backup file for recovery, in case special sheet was deleted
            ActiveWorkbook.Save
            sLastSheet = oSht.Name
      Else
            ' don't care, save null string
            sLastSheet = ""
      End If
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      ' reopen saved workbook if Special Sheet accidentally deleted
      ' only special sheet name was stored
      If (sLastSheet = "") Then Exit Sub
      
      ' check if sheet deleted
      Dim bSheetFound As Boolean
      On Error Resume Next
      bSheetFound = (Worksheets(sLastSheet).Name = sLastSheet)
      On Error GoTo 0
      
      If Not bSheetFound Then
            ' special sheet was deleted
            MsgBox "You cannot delete Special Sheet."

            ' reopen the active workbook from backup saved by SheetDeactivate
            Application.DisplayAlerts = False
            Workbooks.Open ActiveWorkbook.FullName
      End If
End Sub

i believe in Excel after 2010, there's a Worksheet_BeforeDelete event, simplifying this problem.