Delete Sheet event?
 

Is there a way to see if user selects "Delete Sheet"?

I am trying to delete custom doc properties attached to that sheet when user
delete a sheet - how do I trigger this event? I don't see any ID number for
"Delete Sheet"

Thanks for any suggesstion...

Delete Sheet event?
 

Here is one way

Put this code in a standard code module


Public shName As String

Sub Deletesheet()
Dim oWS As Object
On Error Resume Next
Set oWS = Sheets(shName)
If oWS Is Nothing Then
MsgBox shName & " has been deleted"
End If
End Sub



Put this in ThisWorkbook



Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
shName = sh.Name
Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dev" wrote in message
...
Is there a way to see if user selects "Delete Sheet"?

I am trying to delete custom doc properties attached to that sheet when
user
delete a sheet - how do I trigger this event? I don't see any ID number
for
"Delete Sheet"

Thanks for any suggesstion...

Delete Sheet event?
 

I believe you could combine the Workbook_SheetDeactivate and
Workbook_SheetActivate event handlers to accomplish this. Try this code in
the ThisWorkbook module of your project. The Deactivate event is triggered
before the sheet is deleted and saves the sheet name to a variable. The
Activate event is triggered after the sheet is deleted, which tests for the
existence of the previous active sheet.


Dim strLastSheet As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim wksTemp As Worksheet

On Error Resume Next
Set wksTemp = Worksheets(strLastSheet)
If wksTemp Is Nothing Then MsgBox "Sheet Deleted"

End Sub

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

"Dev" wrote:

Is there a way to see if user selects "Delete Sheet"?

I am trying to delete custom doc properties attached to that sheet when user
delete a sheet - how do I trigger this event? I don't see any ID number for
"Delete Sheet"

Thanks for any suggesstion...