Copying Workbook Event Procedure Programatically

Bill
here is a routine I did a while ago to do this;
You will have to change it to reference the correct
event & sheet

Sub Modify_Modules()
'===================================================================
'= Procedure: Modify_Modules =
'= Type: Subprocedure =
'= =
'= Purpose: Add a Procedure to a module including Sheets and =
'= Thisworkbook =
'= Parameters: None =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |13-May-00| Ivan F Moala | Created =
'===================================================================

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your ChangeEvent Procedure OR SubRoutine
SubName = "Private Sub Workbook_SheetChange(ByVal Sh As Object," & _
"ByVal Target As Excel.Range)" & LF

'Your Procedure
Proc = "If Target.Row = 1 Then" & LF
Proc = Proc & Tabs & "MsgBox " & Ap & "Testing row number =" & Ap & " & Target.Address" & LF
Proc = Proc & "End If" & LF

'Use activeWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub

Ivan

Ivan,

Thanks for that macro. I came upon this bit of code which also does the trick:
-----------------
' Create an object reference
' for the workbook event handlers
Set WBCodeMod1 = Workbooks(CurrentTemplate) _
.VBProject.VBComponents _("ThisWorkbook").CodeModule

Set WBCodeMod2 = Workbooks(CurrentClassFile) _
.VBProject.VBComponents("ThisWorkbook") _
.CodeModule

'Copy the Workbook level Event handlers
WBCodeMod2.insertlines 1, _
WBCodeMod1.Lines(1, WBCodeMod1.countoflines)
------------
Bill

Bill, thanks for that bit of code....

Ivan