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

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 2

Yep, this works, but its a bit of a pita. There is a "new worksheet" event, but that only fires when a worksheet in "inserted" not copied!

I am working on a solution, I spent so much time messing around with worksheet count and such rubbish I'm starting to hate Excel!!!

Ross

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.