+ Reply to Thread
Results 1 to 10 of 10

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

Hybrid View

  1. #1
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: 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
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  2. #2
    Registered User
    Join Date
    12-03-2010
    Location
    Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    16

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

    I tried using this and while it did not break anything it also did not add the code into the newly created workbook. Thanks.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1