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

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.

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.

That might be an option. Securing workbook is not an option as I want to provide an option to the user to import some sheets, so when the do Copy or Move from old version of the file, it would not add new sheets, instead it will update the existing sheet in the file with data from copied/moved data. – Adarsha Apr 6 '09 at 22:25 "SheetActivate event will fire under all of those circumstances" It does not.. when multiple sheets are copied over it fires only once for the first sheet.. Maintaining a separate sheets collection is a pain, So as of now I have chosen to disable Move/Copy sheets option from Ply menu, so user can not move sheets from one version of the file to other.

Also I handled New Sheet to block new sheets being added. So as of now I am done with that tool. But hope to see more robust events modal in next version of Excel than one Joel designed long back.

– Adarsha May 7 '09 at 15:44.

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.

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 won't be able to rename, add or delete sheets. This is working pretty well for now.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.