Sheet delete event

[Otto Moehrbach]

Excel 2007, Win 7
I have a bunch of sheets, each named a person's name. I have a list of
these names that I use in a DV cell. When the user deletes one of these
sheets, I would like for the list to be changed to reflect that sheet
deletion. How can I capture the event of a sheet deletion? Thanks for your
time. Otto

 

[JLGWhiz]

I was thinking that you should be able to use the SheetDeactivate event for
the workbook with a public variable to track the sheet count, but I couldn't
get it to work in the first couple of tries in xl03. First it did not
recognize the deletion by code as a deactivate event, then when I added
Select to force it to recognize the change, it gave me an erroneous count on
the sheets. So, I gave up on that approach.

 

[Otto Moehrbach]

Thanks for you help. I setup the Sheet_Deactivate in the Workbook module
(not the sheet module) and it fired when I deleted a sheet and it returned
the removed sheet name in the variable Sh. Thanks again for that idea.
Otto

 

[Gary''s Student]

One of the thing that happens when the active sheet is deleted is that
another sheet is activated. We need to keep track of the number of sheets
and when this value drops, alert that a sheet has been deleted. In the
woriksheet code area of each worksheet, insert:

Private Sub Worksheet_Activate()
If IsEmpty(wCount) Then
wCount = Worksheets.Count
Exit Sub
Else
If wCount > Sheets.Count Then
MsgBox "A sheet has been deleted"
Exit Sub
End If
End If
If wCount < Sheets.Count Then
MsgBox "A sheet has been added"
wCount = Sheets.Count
End If
End Sub


and in a standard module, insert the single line:

Public wCount As Integer


This solution may not be 100% solid, but you get the general idea.

 

[JLGWhiz]

I must have been in sleep mode when I tried this the first time. If the
public variable is initialized in the public module it works fine. But if
it is initialized in the private module, the value is zero in the public
module and a bad result occurs when using the variable after the event code
runs. I don't know if this is by design or an anomaly, but keep it in mind
as you develop your code.