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

Hi Alagard

I'll be glad to follow up on this. However, until this is resolved

Read the Forum Rules then PM me that you intend following them, otherwise I will remove your posting rights.

you're under somewhat of a quarantine.

RoyUK...has this issue been resolved?

John