Workbook_SheetDelete???

L

Line

Hi everyone

Is there a function opposite to Workbook_NewSheet???

I have a menu that list all the sheets in a workbook that updates itself
everytime a sheet is added, I would like it to update when a sheet
is deleted as well. Any way it can be done?

Thanks a lot

Lynn
 
B

Bob Phillips

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

(remove nothere from email address if mailing direct)
 
Top