Event: Workbook_SheetDelete

[Han]

Abbreviated Question #1:
Is there an event such as "Workbook_SheetDelete" in existence that I
could use? I found a "Workbook_SheetNew," so I figured there would be
a counterpart. I need my code to execute whenever a worksheet is
deleted.

Abbreviated Question #2:
Where do I put the event code? In "ThisWorkbook" under "Microsoft
Excel Objects" or somewhere else?

---------------------------------------------------------------------
Extended Explanation for Question #1 and #2:
(You only need to read the text below if it will help you answer my
questions)

My workbook has a sheet that is "VeryHidden." For the sake of
avoiding confusion, I shall call this sheet by its CodeName: Sheet1.
Sheet1 keeps track of sheets I create after Sheet6 and looks something
like this:

No. Name CodeName
1 Cap Sheet7
2 Bottle Sheet8
3 Label Sheet9

Another sheet is dependent on Sheet1 and due to the way I have written
the code it is imperative that there are no empty cells in between rows
like this:

No. Name CodeName
1 Cap Sheet7

3 Label Sheet9

The user has the option of deleting a sheet. Say, they delete Sheet8
(No. 2, Bottle). I would like, upon the act of deletion, for this
sheet to update itself to look like this:

No. Name CodeName
1 Cap Sheet7
2 Label Sheet9

I don't care to change the CodeName, but changing the number ("No.")
would be nice.

The code I am planning on using (referenced from another post in Google
Groups) to delete the empty row is:

Private Sub Worksheet_Delete()

Sheet1.Select
activesheet.Columns("A").SpecialCells _
(xlCellTypeBlanks).EntireRow.Delete

End Sub

It works perfectly because Column A will always contain the header,
"No." Part of my code elsewhere temporarily makes Sheet1
"Visible" to run my macros, so Sheet1 being "VeryHidden" should
also not be an issue.

Question #1:
What I am looking for is a way to activate "Worksheet_Delete" whenever
a worksheet is deleted, but I am not aware of a any such Worksheet or
Workbook event. The closest thing I found was
"Workbook_SheetDeactivate." There is also an event called
"Workbook_SheetNew," therefore I figured there would be a counterpart;
perhaps something like "Workbook_SheetDelete." To the best of my
knowledge, the code contained between:

Private Sub Workbook_SheetDeactivate()
...
End Sub

will run whenever a worksheet is deactivated. Correct me if I am
mistaken.

I realize this could all be simplified by adding a button, but I do not
want to burden the user with more buttons. Any suggestions would be
greatly appreciated.

Question #2:
Also, I am not extremely proficient with VB, so I am unsure of where to
place the code for an event such as "Workbook_SheetDeactivate." Should
it be place in "ThisWorkbook" under "Microsoft Excel Objects" or
somewhere else?

 

[Doug Glancy]

Han,

There is no such event, but you can use the Sheet Activate event to track it
after it's happened. Do a search in Google Groups on "sheet delete event
excel" to read past replies about this from Chip Pearson and others.

hth,

Doug

 

[Bob Phillips]

This code emulates it


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)

 

[Ikaabod]

I would probably use the beforesave event, then track the sheets versus
the cell data in there (unless you need it to do this immediately for
the users sake). My understanding is that this sheet is used solely
for your purposes though. I could be wrong.

In any case, I hope that helps.
-Ikaabod

 

[Han]

Thank you.

 

[Han]

Thank you.

 

[Han]

Wow, I would have never figured that out. This should work perfectly
after I place my code where your message box is.

I have never used the symbol "o" in front of anything. This I will
have to look up. It looks to be extremely useful.

Thank you very much for your help.

 

[Han]

Thank you.

 

[Dave Peterson]

Like in oWS?

That's Bob's naming convention. He wants to remind himself that oWS is declared
as an Object.

 

[Han]

Oh yeah, what do you know? Bob did declare "oWS" as an object. I
didn't see that, so I thought it was some sort of voodoo magic. I'm
such a newb.

 

[Dave Peterson]

MS has a knowledge base article for what they recommend:
http://support.microsoft.com/kb/q110264/