How to capture worksheet being added through Copy/Paste in Excel VBA

Question

I am trying to capture worksheets being copied in to a workbook from another workbook.
Workbook_NewSheet event does not trigger when the sheets are copied from another workbook. It is triggered only if the user manually inserts them through (Insert->Worksheet menu option), or when you add a new sheet through VBA as ThisWorkbook.Worksheets.Add.

What I am trying to capture is basically a Paste operation which is resulting in a new sheet.

This might be from any of the below user actions:

1. User copies an existing sheet by dragging it holding Control Key (which adds a new sheet)
2. User copies sheet/s from another workbook
3. user moved sheets from another workbook

or any of the below VBA code:

SourceWorkbook.Sheets(“SourceSheet”).Copy Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'copy across workbook'  
SourceWorkbook.Sheets(“SourceSheet”).Move Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'move across workbook'  
ThisWorkbook. Sheets(“SheetName”).Copy 'copy within workbook'  

If you know any way of capturing this action/macro results within VBA that would be greatly helpful.

Please note that I do not want to avoid such an user action (so i do not want to secure the workbook) but I want to handle the pasted sheet programatically to verify the data, and if the similar sheet already exists then update the existing sheet rather than having same data in two sheets.

Answer 1

When a sheet is copied, its name will always end with "(2)", or at least ")". You could check on that like this

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like "*(2)" Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
    End If
End Sub

Answer 2

The SheetActivate event will fire under all of those circumstances. Obviously it will fire under a lot of other circumstances too. This sounds like a royal pain, but you could maintain your own collection of worksheets and compare your collection to the ThisWorkbook.Sheets collection to see if something was added/deleted.

If you're trying to prevent it, you might consider protecting the workbook structure instead of doing it in code.

Answer 3

The way I have it implimented is

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
ToggleMenuOptions False, 848, 889
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
ToggleMenuOptions True, 847, 848, 889
End Sub

Public Function ToggleMenuOptions(bToggle As Boolean, ParamArray ControlID() As Variant) As Boolean
'848 Move or Copy Sheet...
'889 Rename Sheet
'847 Delete Sheet
On Error GoTo lblError
Dim oControl As CommandBarControl, oControls As CommandBarControls, iControl As Integer
If IsMissing(ControlID) Then
    ToggleMenuOptions = False
    Exit Function
End If

For iControl = LBound(ControlID) To UBound(ControlID)
    For Each oControl In Application.CommandBars.FindControls(ID:=ControlID(iControl))
        oControl.Enabled = bToggle
    Next
Next
ToggleMenuOptions = True
Exit Function
lblError:
    If Err.Number Then
        ToggleMenuOptions = False
        Exit Function
    End If
End Function

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "Please use Add New Project option in custom Toolbar to add new sheets!!", vbExclamation, "Not Supported"
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End Sub

So my users wont be able to rename, add or delete sheets. This is working pretty well for now.

Answer 4

The only way I can think of doing this without maintaining a separate sheets collection is to maintain a static array of sheet names (or sheet codenames) and compare this to the actual sheets in the workbook each time the SheetActivate event fires to detect any additions. If you don't want to/can't keep the list in an array you could always use a hidden sheet to store the list. Whether this is any more or less of a pain than maintaining a separate collection is debatable :)

Answer 5

I am working on something similar but cannot block any of the user menu actions. I have sheets whose type are important - each sheet is either a Master or Slave - each Master sheet sums over the Slave sheets beneath it and I need to keep these formula clean.

Rather than maintain a list of sheets in an extra hidden sheet, I am defining 2 hidden names on each sheet recording the offset of the index of the Sheet to its linked Master sheet, and a reference to the linked Master sheet. So if my sheet is (say) +2 tabs from its Master sheet, then on Sheet activate/deactivate (not sure which of these is better to track at this stage) this offset will have changed if anything gets inserted, deleted or moved. This covers most or all of the events that would arise from moving or copying sheets.

If the sheet has been moved, I cycle through the workbook and calculate new Master/Slave index references for every sheet.

Will post code when I get this reasonably stable but it seems like a scheme that would work in a wide variety of circumstances.