Worksheet Delete event?

[malik641]

Is there any way I can write a macro to perform when a worksheet is deleted?

Small example:
You have sheets 1,2, and 3. Each named "Sheet1", "Sheet2", and "Sheet3", respectively.

If you were to delete Sheet1, have a MsgBox pop up stating which sheet has just been deleted (in this case it would say "Sheet1 has been deleted.").

How would you approach this??

Thanks in advance

[geekgirlau]

To my knowledge there is no event associated with deleting a sheet (I'm sure someone will correct me if I'm wrong ). Depending on the situation, if you have a particular workbook you want to control, you could create an array listing all of the sheets that should be there, and use another event (before save?) to loop through the sheets and check that they all still exist in the workbook.

[Bob Phillips]

Here you are a delete sheet event.

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

[geekgirlau]

[Ivan F Moala]

Also ..... see here

http://www.xtremevbtalk.com/showthread.php?t=147139

[MWE]

Also ..... see here

http://www.xtremevbtalk.com/showthread.php?t=147139

the above suggests a way to "know" if a sheet has been deleted via a normal Delete Worksheet command (which I think is what the originator of this thread wanted). The problem with renameing sheets can, in general, be avoided if the sheet references used are the "VBE names" for the sheets, not the tab names. Users can change tab names and rearrange tabs, but it is quite unlikely that they will do anything in the VBE.

This is an interesting thread.

[malik641]

The problem with renameing sheets can, in general, be avoided if the sheet references used are the "VBE names" for the sheets, not the tab names. Users can change tab names and rearrange tabs, but it is quite unlikely that they will do anything in the VBE.

The problem with VB that I've noticed is when you delete a sheet, the VB Name is also switched in the VBE (but not changed in the VB Project window)

For example:
Place this code in a new workbook in "This Workbook":

Sub CheckSheet2()
MsgBox Worksheets(2).Name
End Sub

Execute this procedure. Sheet name (as long as nothing is changed) will be "Sheet2". If you Delete Sheet1 and run the macro again, the MsgBox will display "Sheet3", so sheet 2 is NOW sheet 3 according to VB names. It's hard to work with VB names if a sheet is deleted.

As for XLD's submission, I will test it when I get to work and let you know. Thanks

[mvidas]

Joseph,

I think MWE was talking about the codename property of the sheets:

Sub CheckSheet2() 
    MsgBox Worksheets(2).CodeName 
End Sub

Its what you see before the sheet's name in the VBE. Integrating that into the above code (both in this thread and Ivan's suggestion) is a little different, but still can be done.

Matt

[MWE]

Joseph,

I think MWE was talking about the codename property of the sheets:[vba]Sub CheckSheet2()
MsgBox Worksheets(2).CodeName
End Sub [/vba]Its what you see before the sheet's name in the VBE. Integrating that into the above code (both in this thread and Ivan's suggestion) is a little different, but still can be done.

Matt

mvidas is correct; I was referring to the codename.

There are 3 ways to reference a given sheet:

1. sheet index, e.g., Sheets(1)
2. tab name, e.g., Activesheet.Name
3. code name, e.g., Activesheet.CodeName

The Tab name can be changed at any time by anyone (assuming the sheet is not protected). The sheet index is changed anytime the tab sequence is changed, a sheet is added, a sheet is deleted, etc. But the codename is assigned when the sheet is created and unless someone explicitly goes into the VBE (or uses VBA) and changes it, the codename does not change.

CodeNames and TabNames start out the same, but after a while are often quite different due to renaming of tabs, etc. It gets really confusing when you delete some sheets, add others, etc., because you can end up with the same codename and sheetname, but they do not refer to the same sheet.

Conceptually what I suggested in an earlier reply is quite possible. Subsequent to my reply, I roughed out some code that does monitor all sheets (by codename) for the addition of new sheets or the deletion of an old sheet. It is an interesting problem and probably has some utility. Presently I store codenames in a "static" array, but they could just as easily be stored in a special sheet (unless someone deletes that sheet!). The procedures are fooled if you change the codename in the VBE or programmatically.

[mvidas]

For a modified version of the above code (prevents it from triggering when renamed)

This workbook code:

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
shName = GetSheetNameFromCodeName(sh.CodeName)
Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
End Sub

Module code:

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 Sub

Public Function GetSheetNameFromCodeName(ByVal pCodeName As String, Optional wb) As String 
'courtesy of xld, next post
    If IsMissing(wb) Then Set wb = ThisWorkbook 
    GetSheetNameFromCodeName = wb.VBProject.VBComponents(pCodeName).Properties("Name") 
End Function

Matt

[Bob Phillips]

Function GetSheetNameFromCodeName(ByVal TheCodeName As String) As String
 Dim ScriptCont As Object, CodeText As String
 Set ScriptCont = CreateObject("msscriptcontrol.scriptcontrol")
 CodeText = "Function TheSheetName(WB)" & vbCrLf & " Dim WS" & _
  vbCrLf & " For Each WS In WB.Sheets" & vbCrLf & "  If WS.CodeName = """ & _
  TheCodeName & """ Then" & vbCrLf & "   TheSheetName = WS.Name" & vbCrLf & _
  "   Exit For" & vbCrLf & "  End If" & vbCrLf & " Next" & vbCrLf & "End Function"
 ScriptCont.Language = "VBScript"
 ScriptCont.AddCode CodeText
 GetSheetNameFromCodeName = ScriptCont.Run("TheSheetName", ThisWorkbook)
 Set ScriptCont = Nothing
End Function

Isn't this a bit easier

Public Function GetSheetNameFromCodeName(ByVal pCodeName As String, Optional wb) As String
    If IsMissing(wb) Then Set wb = ThisWorkbook
    GetSheetNameFromCodeName = wb.VBProject.VBComponents(pCodeName).Properties("Name")
End Function

[mvidas]

Sure is
I forgot about the .Properties property.. I tried just .Name but it only returned the codename again

edited my code above to reflect that
Thanks!

[Bob Phillips]

Sure is
I forgot about the .Properties property.. I tried just .Name but it only returned the codename again

edited my code above to reflect that
Thanks!

As I have said before ... the object browser is your friend

[malik641]

mvidas is correct; I was referring to the codename.

Gotcha.
Thanks for that summary of how that worked too. That helped me understand what's going on a little more when shifting sheets/deleting etc.

As for the code submission (revised), it's working great. I'm just wondering though if I have this straight.

What this code is doing is:
1. When a sheet is deactivated, it takes that sheet's name and sends it to the public function
2. Public function takes that name and finds the code name for that sheet
3. Public function sends the codename back to the Worksheet_Deactivate event
4. After one second, the _Deactivate event sends that code name to the DeleteSheet procedure
5. DeleteSheet procedure displays MsgBox of the deleted sheet's name IF it was deleted to begin with

Right???

BTW, I'm not even going to go NEAR mvidas' "unrevised" (before edited) code ...reDICulous!! I have NO idea what was going on there...too advanced for me (...look at xld's quote block of the function I'm talking about)

[Bob Phillips]

What this code is doing is:
1. When a sheet is deactivated, it takes that sheet's name and sends it to the public function
2. Public function takes that name and finds the code name for that sheet
3. Public function sends the codename back to the Worksheet_Deactivate event
4. After one second, the _Deactivate event sends that code name to the DeleteSheet procedure
5. DeleteSheet procedure displays MsgBox of the deleted sheet's name IF it was deleted to begin with

Not quite.

1, When a sheet is deactivated, it's codename is sent to the public function.
2. Public function takes that codename and finds its current Excel name.
3. Public function sends Excel name back to the Deactivate event.
4. Deactivate event fires a timed macro, to act in one sec - this is to enable Deactivate to complete and ensure the sheet is deleted.
5. DeleteSheet checks if the name passed exists or not, and says so if not. Remember this gets called on any deactivate, so we have to test that it doesn't exist to identify deleted sheets.

It works on the principle that when you delete a worksheet, the daectivate is called before it is deleted. So you can still get details about it before it disappears into the ether, but you can't test anything as it still exists, hence OnTime.

.

[MWE]

per my earlier replies, attached is a spreadsheet with code that monitors adds and deletes of worksheets. I use the codename to keep track of things and originally "displayed" the codename that was added or deleted. Since most people would rather see the tab name, I tried that and encountered some interesting things:
1. the codename is initially blank so when my procedure tests for "add" all it has to look for is a blank codename for a worksheet. In my code, the retained name is the worksheet tab name which is what the codename will be when Excel get around to providing the codename. I think this is robust, but others may have other ideas.
2. the delete is interesting too. Since the code detects that a worksheet has been deleted AFTER the delete, the worksheet no longer exists so there is no worksheet tab name to "display". So, for now, my code displays the tab name for an add but the codename for a delete. On could keep track of tab names and codenames but that will get messy.

I have not done much re error checking and testing, but it seems to work ...

[Bob Phillips]

1. the codename is initially blank so when my procedure tests for "add" all it has to look for is a blank codename for a worksheet.

Is that a problem that you need a solution for?

[MWE]

Is that a problem that you need a solution for?

Thanks for the offer, but no solution is really required. The test seems to work well and is a lot simplier than comparing current worksheet names against the "archived" list of names. The code is more an exercise in how things might be done than anything else.

[malik641]

Not quite.

1, When a sheet is deactivated, it's codename is sent to the public function.
2. Public function takes that codename and finds its current Excel name.
3. Public function sends Excel name back to the Deactivate event.
4. Deactivate event fires a timed macro, to act in one sec - this is to enable Deactivate to complete and ensure the sheet is deleted.
5. DeleteSheet checks if the name passed exists or not, and says so if not. Remember this gets called on any deactivate, so we have to test that it doesn't exist to identify deleted sheets.

It works on the principle that when you delete a worksheet, the daectivate is called before it is deleted. So you can still get details about it before it disappears into the ether, but you can't test anything as it still exists, hence OnTime.

.

Gotcha.
As for MWE's SheetMonitoring example, I'll be taking a look at that later. Thanks for the submission! I'm sure I'll find a good use for this.

[mvidas]

Regarding my unrevised code in http://www.vbaexpress.com/forum/show...0&postcount=11 it is really just a use of the scriptcontrol, which allows you to send a function/sub as a string to the scriptcontrol and have it run. Useful if you have code on a internet/intranet server or in a local text file that you want to reference without having to keep it in the workbook itself. It is one way to have a central code library for a group, and the one change on the server can prevent you from having to redistribute a workbook/routine to users.
In the function itself, the ScriptCont object is initialized as a scriptcontrol object, and the code text is really just the following function in string form:

Function TheSheetName(WB)
 Dim WS
 For Each WS In WB.Sheets
  If WS.CodeName = "<desired sheet name inserted here at runtime>" Then
   TheSheetName = WS.Name
   Exit For
  End If
 Next
End Function

As I thought that was the only way to compare a codename in string form to the codename of sheets of an active workbook. Then xld reminded me that there is a much easier way to do that