Worksheet-DELETE event???

P

Prosenjit Banerjee

New Member
Joined
Mar 19, 2002
Messages
19
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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.
Click to expand...


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>
 
Upvote 0
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
 
Upvote 0
I think you would have to intercept all possible ways of deleting the sheet. Can I ask what the purpose is?
 
Upvote 0
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.
 
Upvote 0
saavedrah said:
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.
Click to expand...


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 :

Code: 
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
 
Upvote 0
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... :eeek:


Code: 
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
 
Upvote 0
saavedrah said:
Hello,

What about before deleting the worksheet ?

Thanks
Click to expand...

Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Undo Stack gets cleared running VBA - Possible Solution to fix this?
Replies
5
Views
179
kellman
K
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
352
tixarah
T
9
  • Question Question
VBA code deleted in macro enabled workbook when i overwrite workbook with save as
Replies
3
Views
93
RoryA
RoryA
H
  • Solved
Scatter Chart to Show Durations
Replies
2
Views
127
HJay
H
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
756
Sheik
S

Forum statistics

Threads
1,224,891
Messages
6,181,614
Members
453,057
Latest member
LE102024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top