Workbook_SheetDelete???

[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

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

"Line" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Line]

"Bob Phillips" wrote:

> One way


And a simple one... Many many thanks

Lynn