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.


"Otto Moehrbach" wrote in message
...
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

"JLGWhiz" wrote in message
...
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" wrote in message
...
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