Consulting

Results 1 to 20 of 20

Thread: Solved: Worksheet Delete event?

  1. #1
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location

    Question Solved: Worksheet Delete event?

    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




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  2. #2
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,282
    Location
    Here you are a delete sheet event.

    Put this code in a standard code module

    [VBA]
    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
    [/VBA]

    Put this in ThisWorkbook

    [VBA]
    Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
    shName = sh.Name
    Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
    End Sub
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location

  5. #5
    VBAX Contributor Ivan F Moala's Avatar
    Joined
    May 2004
    Location
    Auckland New Zealand
    Posts
    185
    Location
    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by Ivan F Moala
    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.
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  7. #7
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by MWE
    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":
    [VBA]
    Sub CheckSheet2()
    MsgBox Worksheets(2).Name
    End Sub

    [/VBA]

    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




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  8. #8
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  9. #9
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by mvidas
    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.
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  10. #10
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    For a modified version of the above code (prevents it from triggering when renamed)

    This workbook code:[vba]Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
    shName = GetSheetNameFromCodeName(sh.CodeName)
    Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
    End Sub[/vba]

    Module code:[vba]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 [/vba]
    Matt
    Last edited by mvidas; 09-23-2005 at 10:49 AM. Reason: updated clunky code

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,282
    Location
    Quote Originally Posted by mvidas

    [VBA]
    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[/vba]
    Isn't this a bit easier

    [VBA]
    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
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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!

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,282
    Location
    Quote Originally Posted by 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!
    As I have said before ... the object browser is your friend
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by MWE
    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)




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,282
    Location
    Quote Originally Posted by malik641
    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.

    .
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    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 ...
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,282
    Location
    Quote Originally Posted by MWE
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by xld
    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.
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  19. #19
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by xld
    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.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  20. #20
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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:[vba]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[/vba]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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •