Workbook Event on Sheet Deletion

[erock24]

is there a macro I can use to call another macro everytime a worksheet is deleted. For example if I right click on a sheet tab and click delete, can I then have a macro run automatically.

 

[lenze]

I am not aware of an event that will do that!! What do you reall want to do?
lenze

 

[erock24]

I have a macro that creates a table of contents. If a user deletes a sheet they need to run the macro to update the TOC. No big deal. but if excel would do it automatically I thought that would be cool.

Here is an Idea I don't know...what if the count of the sheets was stored as variable and everytime the worksheet count changed the macro would kick off???

 

[GTO]

Technically you could probably build an array of worksheet names and have this checked every time a worksheet was activated, but why not just update the TOC in BeforeSave?

Mark

 

[Peter_SSs]

What about just using the Worksheet_Activate event on the sheet that contains the TOC. It won't update immediately when a sheet is deleted, but what does that matter if you are not looking at the TOC sheet?

If it does matter, because, say, you might print without activating the TOC sheet, then try using, in the ThisWorkbook module, something like this?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetActivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)<br>    <SPAN style="color:#007F00">' TOC refresh</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This will run every time a sheet is activated but, unless the code is slow, it shouldn't be noticeable. At least every time a sheet is deleted (manually), a different sheet is activated so the code would run.

 

[Ivan F Moala]

Yes can can Simulate this!

Place in ThisWorkBook code Module

Option Explicit
'// http://www.xtremevbtalk.com/showthread.php?t=147139
Dim shName As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Avail As Variant

On Error Resume Next
Avail = Sheets(shName).Range("A1")
If Err Then
    MsgBox shName & " has been Deleted ...Put your routine here to run?"
End If
On Error GoTo 0

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Application.DisplayAlerts = False
shName = Sh.Name
'Application.DisplayAlerts = True
End Sub

This works on the fact that the Workbook_SheetDeactivate event is triggered before
the SheetActivate event and stores the Sheet name in variable shName.
The sheetActivate event runs a little routine that
tries to get a value from the lastsheet, which is
shName (from the DeactivateEvent), the on Error Resume next is put there so that if the sheet no longer
exists it generates an error object which is evaluated.

THIS IS WHERE you can put your routine you want
to run when a sheet is deleted.

 

[GTO]

@Ivan F Moala:

Hi Ivan and Peter too

Say, thank you for that! Quite frankly I had never even thought of whether the sheet deacitivate would occur when its going away (being deleted), but of course it does. That is nifty!

Mark

 

[erock24]

thank you for the replies...these helped alot.

 

[Arf88]

Thanks ! Worked nicely!