Detecting When User Deletes a Sheet

[boyd.james]

Is any event raised when a sheet gets deleted? I'd like to remove a
control button upon certain sheets being removed.

Thanks,

James

 

[Guest]

When a worksheet is deleted by user action, when it is deleted another sheet
becomes the active sheet. That causes the
Workbook_SheetActivate() event to fire. You could put code in that routine
to see if the sheet(s) you're interested in still exist in the book or not.
Of course, the Workbook_SheetActivate() event is going to fire at other
times: any time a new sheet is selected by the user.

Code might look something like this - although someone else may have a
better way/event to us, and perhaps an even faster test to see if the
sheet(s) still exists.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim anyRange As Range
'just so you can see when it fires
MsgBox "SheetActivate Fired"

On Error Resume Next
'can refer to any cell on the sheet, since all you
'want to know is if that sheet is still in the
'workbook or not
Set anyRange = Worksheets("SheetOfInterest").Range("A1")
If Err <> 0 Then
Err.Clear
MsgBox "Sheet Does NOT Exist"
'the sheet does not exist
'code here to deactivate/remove your button
'but remember that this sheet is never going
'to exist again after this action is done
'
'could exit now or fall through to test for
'other sheets in similar fashion
On Error GoTo 0
Exit Sub
End If
MsgBox "Sheet is still in the workbook"
Set anyRange = Nothing 'release used resource
On Error GoTo 0 ' clear error trapping when all done

End Sub

 

[Guest]

No, there is not any specific event, BUT you can achieve that with a little
effort:
* In ThisWorkbook declare a module variable to hold the last deactivated sheet
* do little programming in SheetDeactivate and SheetActivate events to
verify the last deactivated sheet is still alive:

code sample in ThisWorkbook:

Private mOldSheetName As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim oldFound As Boolean
Dim ws As Worksheet
oldFound = False
For Each ws In Worksheets
If ws.Name = mOldSheetName Then
oldFound = True
Exit For
End If
Next
If Not oldFound Then
Debug.Print "Sheet deleted: " & mOldSheetName
End If
End Sub

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