Another approach would be to instantiate and use Application level events:

See Chip Pearson's site on this topic (or use Excel VBA Help)
http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...
Hi,
If each _Change event for each newly added sheet runs similar code, you
could use a Class module instead. That is, you don't need to access the
vbide programatically, and you don't need to duplicate similar code several
times.

1. Create a class named clsSheetEvent:
'----------------------------------------------------------------
Public WithEvents Worksheet As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Parent.Name & " -- " & Target.Address
'... your event code here
'
End Sub

Private Sub Class_Terminate()
Set Worksheet = Nothing
End Sub
'----------------------------------------------------------------

2. Add a clsSheetEvent global variable. In a module:
Public wshEvent As clsSheetEvent

3. In the sub that adds a sheet:
Public Sub AddSheet()
Dim wsh As Worksheet

Set wsh = ActiveWorkbook.Worksheets.Add
Set wshEvent = New clsSheetEvent 'Create the clsSheetEvent
Set wshEvent.Worksheet = wsh 'Sets its Worksheet member to
newly added one
End Sub

Now, after running AddSheet, change a cell in the newly created sheet, it
will executed the _Change from the class.
Here, i declared only one clsSheetEvent variable (wshEvent), but you
could, for example, store them in a collection instead to have as many as
you want.
The interesting thing is that you don't need to add the code in each
sheet.

I hope this helps,

Regards,
Sebastien