Using a macro to add code to a newly created workbook.

Hi Alagard

Try this

Option Explicit
Sub CreateEventProcedure()
' Adapted from http://www.cpearson.com/excel/vbe.aspx
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    '    Const DQUOTE = """"    ' one " character
    Set VBProj = Workbooks("Book2").VBProject
    Set VBComp = VBProj.VBComponents("sheet1")
    Set CodeMod = VBComp.CodeModule
    LineNum = 1
    With CodeMod
        LineNum = LineNum
        .InsertLines LineNum, "Option Explicit"
        LineNum = LineNum + 1
        .InsertLines LineNum, "' Adapted from http://www.cpearson.com/excel/vbe.aspx"
        LineNum = LineNum + 1
        .InsertLines LineNum, "Private Declare Function sndPlaySound32 Lib ""winmm.dll"" _"
        LineNum = LineNum + 1
        .InsertLines LineNum, "Alias ""sndPlaySoundA"" (ByVal lpszSoundName As String, _"
        LineNum = LineNum + 1
        .InsertLines LineNum, "ByVal uFlags As Long) As Long"
        LineNum = LineNum + 1
        .InsertLines LineNum, ""
        LineNum = LineNum + 1
        .InsertLines LineNum, "Private Sub Worksheet_Change(ByVal Target As Range)"
        ' etc
        ' etc
        ' etc
    End With
End Sub

Place the code in a general Module of Book 1. It'll write the code in Sheet 1 module of Book 2.

John