run macro when worksheet is deleted

[NMeeker]

I have a macro i would like to run whenever a user deletes a worksheet...

I would like the user to be able click as normal to delete,(ie. right clicking the tab and selecting delete worksheet, or selecting delete worksheet from the menu) but would like to run my macro when they select delete.

Any suggestions?

I am using excel 07 if that helps, or i could use another version...
My macro will save a copy of the deleted worksheet as xlveryhidden as a backup..

 

[Tony Miall]

What about a button in the worksheet that runs the macro then deletes the sheet instead of right clicking the tab

 

[Tom Urtis]

Well, what you need is to tie your desired subsequent action to an event that is triggered when a worksheet is deleted. There is no built-in event for that, but here is a link from a couple experts (Ivan and Colo) who provided some methods that might be useful to you.

http://www.mrexcel.com/forum/showthread.php?t=5340

 

[NMeeker]

Tony, its an interesting idea, but it doesn't stop the user from deleting the worksheet without pressing the button, and protecting the sheet doesn't seem like an option considering all the editing the user needs to be able to do.

Tom, Thanks for your suggestion, I think it may prove to be the most helpful idea I have seen yet. However, I think I want to catch it right before the sheet is deleted, not right after. I am trying to save the sheet from being totally deleted.

(typing my thoughts)
Though, I could save all the sheets in a second place, and splash a message asking the user if they want to delete the archival copy as well. But that would leave every delete with a double "are you sure" message. Any suggestions on a way to prevent the "are you sure you want to delete" message when the "USER" deletes the worksheet?

 

[GTO]

Greetings,

Forgetting for the moment any choice as to keeping a "backup copy" or not: Rather than trying to trap the deleting of the sheet, have you considered substituting a "look-alike" commandbar button?

NOT tested and incomplete, but just an example of sneaking in your own button in place of the Delete command under the tab's right-click menu.

I would be thinking in terms of using the workbook's activate and deactivate events to add/delete the custom button, so that if the user has a couple of workbooks open, he's not hiding sheets in both.

There are of course some other concerns, right off the top would be: User "deletes" sheet (so now its veryhidden) then later trys to add a sheet and name it the same as one of the hidden sheets...

Hope this helps,

Mark

Sub CommandSubstitute()
Dim CBar As CommandBar
Dim btnCustDel As CommandBarButton
Dim ctl As CommandBarControl
Dim btn As CommandBarButton
Dim iIndex As Integer
    
    For Each ctl In Application.CommandBars("Ply").Controls
        With ctl
            If .ID = 847 Then
                iIndex = .Index
                .Visible = False
                Exit For
            End If
        End With
    Next
    
    Set CBar = Application.CommandBars("Ply")
    Set btnCustDel = CBar.Controls.Add(Type:=msoControlButton, _
                                       Before:=iIndex, _
                                       Temporary:=True)
                                       
    With btnCustDel
        .Caption = "&Delete"
        .OnAction = "MyMacro"
        .Tag = "SubDel"
    End With
End Sub

Sub DelCmd()
Dim ctl As CommandBarControl
On Error Resume Next
    For Each ctl In Application.CommandBars("Ply").Controls
        With ctl
            If .Tag = "SubDel" Then
                .Delete
            End If
            If .ID = 847 Then
                .Visible = True
            End If
        End With
    Next
End Sub

 

[NMeeker]

GTO, I have a feeling this could be a very helpful way of doing it, but I have very little experience working with commandbar buttons. I have been fiddling with your code a little bit and think it might be on the right track. I am having trouble getting it to run my macro though. I placed your code in "ThisWorkbook" and have been trying to get a simple form to show... with no luck. Also, I am using excel 07 currently, and there are 2 ways for the user to delete a sheet. The first is right clicking the tab for the worksheet at the bottom, and selecting delete, Your code seems to affect that well, though i cant get my form to show... The second way is to click on delete sheet through the Home tab, in the cells group. For this way, your code does not appear to do anything at all. Any idea how to catch that commandbutton?

Also, I have thought about the names of the sheet being an issue. The sheets will be renamed, with the current name and "hidden." If necessary, hidden1, hidden2...

 

[GTO]

GTO... I am having trouble getting it to run my macro though. I placed your code in "ThisWorkbook" and have been trying to get a simple form to show...

If you have the macro called by .OnAction in ThisWorkbook module, move it to a standard sub.

...and there are 2 ways for the user to delete a sheet...The second way is to click on delete sheet through the Home tab, in the cells group. For this way, your code does not appear to do anything at all. Any idea how to catch that commandbutton?

Sorry, but no. I don't know what the "Home tab" or "cells group" is . Hopefully someone with 2007 will jump in .

In pre 2007, the two places I know for the Delete Sheets cmd bttn to be are the Ply (tab shortcut menu) toolbar and the MenuBar under Edit. Though still a bit sloppy, here is a stab at substituting the buttons under Ply and MenuBar. I believe the check for "<= 11" should accurately check if we're running 2003 and before. If you find that the command is also in the MenuBar in 2007 (assuming the MenuBar exists in 2007) then you could REM out this IF test and see if it works.

Try the below in a blank/new workbook, or leastwise in a throwaway copy of yours, lest we accidently KABOOM! your work.

In ThisWorkbook Module:

Option Explicit
 
Private Sub Workbook_Activate()
 
Dim _
CBar                    As CommandBar, _
ctl                     As CommandBarControl, _
ctlSub                  As CommandBarControl, _
btnCustDel              As CommandBarButton, _
btn                     As CommandBarButton, _
ctlEdit                 As CommandBarPopup, _
iIndex                  As Integer, _
bolBail                 As Boolean
 
    For Each ctl In Application.CommandBars("Ply").Controls
 
        With ctl
            If .ID = 847 Then
                iIndex = .Index
                .Visible = False
                Exit For
            End If
        End With
    Next
 
    Set CBar = Application.CommandBars("Ply")
    Set btnCustDel = CBar.Controls.Add(Type:=msoControlButton, _
                                       Before:=iIndex, _
                                       Temporary:=True)
    With btnCustDel
        .Caption = "&Delete"
        .OnAction = "MyMacro"
        .Tag = "SubDel"
    End With
 
    If Application.Version <= 11 Then
 
        Set CBar = Application.CommandBars("Worksheet Menu bar")
 
        For Each ctlEdit In CBar.Controls
 
            If ctlEdit.Caption = "&Edit" Then
 
                For Each ctlSub In ctlEdit.Controls
                    If ctlSub.ID = 847 Then
                        iIndex = ctlSub.Index
                        ctlSub.Visible = False
 
                        Set btnCustDel = ctlEdit.Controls.Add( _
                                                    Type:=msoControlButton, _
                                                    Before:=iIndex, _
                                                    Temporary:=True _
                                                             )
                        With btnCustDel
                            .Caption = "&Delete Sheet"
                            .OnAction = "MyMacro"
                            .Tag = "SubDel"
                        End With
 
                        bolBail = True
                        Exit For
                    End If
 
                Next
            End If
 
            If bolBail Then Exit For
        Next
    End If
End Sub
 
Private Sub Workbook_Deactivate()
 
Dim _
ctl                     As CommandBarControl, _
ctlEdit                 As CommandBarPopup, _
ctlSub                  As CommandBarControl, _
CBar                    As CommandBar
 
    On Error Resume Next
    For Each ctl In Application.CommandBars("Ply").Controls
 
        With ctl
            If .Tag = "SubDel" Then
                .Delete
            End If
            If .ID = 847 Then
                .Visible = True
            End If
        End With
    Next
 
    If Application.Version <= 11 Then
 
        Set CBar = Application.CommandBars("Worksheet Menu bar")
 
        For Each ctlEdit In CBar.Controls
            If ctlEdit.Caption = "&Edit" Then
                For Each ctlSub In ctlEdit.Controls
                    If ctlSub.ID = 847 Then
                        ctlSub.Visible = True
                    End If
                    If ctlSub.Tag = "SubDel" Then
                        ctlSub.Delete
                    End If
                Next
            End If
        Next
    End If
End Sub

In a Standard Module:

Sub MyMacro()
Dim iRetVal As VbMsgBoxResult
    iRetVal = MsgBox("Are you sure you want to delete the selected sheet(s)?", _
                     vbExclamation + vbYesNo + vbDefaultButton2, "")
    
    If iRetVal = vbYes Then
        'Better code than this... Probably run through hidden sheets to see
        'if already exists, etc,,,
        'for simple example only:
        On Error Resume Next
        ActiveSheet.Visible = xlSheetVeryHidden
        
    Else
        'maybe not needed
    End If
    
End Sub

Hope that helps

Mark

PS - you mentioned being inexperienced w/commandbars/commandbar controls. There are certainly those more knowledgeable than me here, but if you have questions, I can comment the code.

 

[NMeeker]

Well, After this experimentation I do believe you have found a very preferable way of accomplishing my goal. In 07 I noticed no difference between using "<= 11" and not. Though, My ribbon issue still exists.

I have started doing some research into how I can replace a command button within a ribbon, the same as you have done for previous versions.

Though, me not knowing much about commandbars, I am definitely stumbling in the dark. So if anyone else comes along with some knowledge on 07's ribbons and wants to take a crack at replacing the delete sheet code, by all means, please help!

In the mean time, if you could edit your post and add comments, I am betting it would be very helpful. It seems like many of the tasks for editing ribbons in 07 look very similar to some of the code you have posted.

I'll keep working on it and if I figure it out Ill post back, Thanks again for the help!!!!

 

[GTO]

...In the mean time, if you could edit your post and add comments, I am betting it would be very helpful... It seems like many of the tasks for editing ribbons in 07 look very similar

Glad to be of what help I can be There's like a 10 minute limit on editing, but here's code w/comments.

Private Sub Workbook_Activate()
Dim _
CBar                    As CommandBar, _
ctl                     As CommandBarControl, _
ctlSub                  As CommandBarControl, _
btnCustDel              As CommandBarButton, _
btn                     As CommandBarButton, _
ctlEdit                 As CommandBarPopup, _
iIndex                  As Integer, _
bolBail                 As Boolean
 
    '// As we are just rotating thru the command buttons on the Ply shortcut//
    '// menu, we can do this straightaway.                                  //
    For Each ctl In Application.CommandBars("Ply").Controls
 
        With ctl
            '// You'll of course want to start reading the topics in help,  //
            '// but basically .ID is for BuiltIn commands, and controls what//
            '// happens when the button is pushed (like OnAction) for the   //
            '// factory buttons.  So... we'll use it to get the right       //
            '// button, and find the button's index, so we can stick the    //
            '// custom bttn in the same place visually.                     //
            If .ID = 847 Then
                iIndex = .Index
                .Visible = False
                Exit For
            End If
        End With
    Next
 
    '// Now we'll set a reference to the commandbar and add a control to it.//
    '// There are different types, we are "replacing" w/the same type to be //
    '// "sneaky".  We're sticking it right Before the now hidden control,   //
    '// and the Temporary, while really unnecessary (as we delete the bttn  //
    '// ea time the wb deactivates) is just a safety I choose to use, as    //
    '// even if my code goes Thunk(!), Excel will kill the button when the  //
    '// app is quit.                                                        //
    Set CBar = Application.CommandBars("Ply")
    Set btnCustDel = CBar.Controls.Add(Type:=msoControlButton, _
                                       Before:=iIndex, _
                                       Temporary:=True)
 
    '// Pretty self-explanatory; I use .Tag as (IMHO) an effective way to   //
    '// find the button later to kill it.                                   //
    With btnCustDel
        .Caption = "&Delete"
        .OnAction = "MyMacro"
        .Tag = "SubDel"
    End With
 
 
    If Application.Version <= 11 Then
        '// Since we're actually looking for sub commands under "Edit", first//
        '// we'll set a reference to the CB...                              //
        Set CBar = Application.CommandBars("Worksheet Menu bar")
 
        '// ...then loop thru the controls on the MenuBar, find "Edit"...   //
        For Each ctlEdit In CBar.Controls
            If ctlEdit.Caption = "&Edit" Then
 
                '// ...loop thru the controls under "Edit" to find "Delete  //
                '// Sheet", again using the .ID                             //
                For Each ctlSub In ctlEdit.Controls
                    If ctlSub.ID = 847 Then
                        iIndex = ctlSub.Index
                        ctlSub.Visible = False
 
                        '// Since we've 'drilled down' to the control, we'll//
                        '// stick our bttn building here...                 //
                        Set btnCustDel = ctlEdit.Controls.Add( _
                                                    Type:=msoControlButton, _
                                                    Before:=iIndex, _
                                                    Temporary:=True _
                                                             )
                        With btnCustDel
                            .Caption = "&Delete Sheet"
                            .OnAction = "MyMacro"
                            .Tag = "SubDel"
                        End With
 
                        '// Just to exit the outer loop, since we're done   //
                        bolBail = True
                        Exit For
                    End If
 
                Next
            End If
 
            If bolBail Then Exit For
        Next
    End If
 
End Sub
 
 
Private Sub Workbook_Deactivate()
Dim _
ctl                     As CommandBarControl, _
ctlEdit                 As CommandBarPopup, _
ctlSub                  As CommandBarControl, _
CBar                    As CommandBar
 
    '// While I try and avoid blinding the code to errors, just included in //
    '// case the button doesn't exist (which shouldn't really happen, so    //
    '// probably a bit sloppy on my part), in which case .Delete will botch //
    '// up a perfectly good day...                                          //
    On Error Resume Next
    For Each ctl In Application.CommandBars("Ply").Controls
 
        With ctl
            '// Use the .Tag to Delete                                      //
            If .Tag = "SubDel" Then
                .Delete
            End If
 
            '// and the .ID to bring back the Builtin control               //
            If .ID = 847 Then
                .Visible = True
            End If
        End With
    Next
 
    If Application.Version <= 11 Then
 
        Set CBar = Application.CommandBars("Worksheet Menu bar")
 
        For Each ctlEdit In CBar.Controls
            If ctlEdit.Caption = "&Edit" Then
                For Each ctlSub In ctlEdit.Controls
                    If ctlSub.ID = 847 Then
                        ctlSub.Visible = True
                    End If
                    If ctlSub.Tag = "SubDel" Then
                        ctlSub.Delete
                    End If
                Next
            End If
        Next
    End If
End Sub

Have a great day,

Mark

 

[NMeeker]

Thanks Mark, that was very helpful!!!

Actually I lucked out and found a FANTASTIC website with some fairly simple steps to editing the Ribbon

This site has all the info you need to edit the Ribbons in 07
http://www.rondebruin.nl/ribbon.html

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<!-- *********************************************-->
<!-- *******Disable WORKSHEET DELETE Controls *******-->
<!-- *********************************************-->
     <commands>
<!-- Run your own macro when you click on a build in control-->
<!-- When you click on Delete Sheet on the Home tab it will run the macro "Archive"-->
<!-- Click on the "generate Callbacks" button in the UI editor to create the Callback-->
<!-- You can copy the Callback then in a module in your workbook and add your code-->
          <command idMso="SheetDelete" onAction="Archive"/>
     </commands>
</customUI>

This code is not for VBA, it is the code for the XML ribbon when using the UIEditor found here:
http://openxmldeveloper.org/articles/customuieditor.aspx

I highly suggest playing around with this for anyone using office 07!!!
I was actually able to catch the "delete sheet" command button in the ribbon using the information from that website fairly easily. When you click delete, it plays my macro with ease and never even tries to delete the worksheet. Plus!! With 07s ribbons, if you edit a ribbon for a single workbook, it will initialize with the workbook, and unload itself when the workbook is closed. So its options are only available for the workbook you intend them for!!!! Fantastic!! Thanks again for all the help everyone!!

Mark, I do expect to be using my program on several different computers, and some may be using older versions of excel. So your code works great and will be very helpful with the older versions Thank you so much. I learned a lot.

Have a FANTASTIC DAY!!
~NMeeker