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;

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.

[Colo]

Hi.
If you do not need the name of deleted sheet, pls copy this into a ThisWorkbook module
and try this.

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

[ 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]

Originally Posted by 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.

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