Is it possible to create a macro with a macro?

[earldunning]

As part of a tool I am building, I copy a worksheet and attach it to an email and send it (all part of code)

What I want to do is put a button and a few dropdowns on the new attachment (I know how to code this).

What I dont know is how could I code it so that the new attachment also gets code put in it so that I can link the button to it? So when a person opens the attachment it has the dropdowns and buttons and will fire code when they use the button? Basically I need to create a macro that creates a macro within that other worksheet.

Ideas?

 

[earldunning]

I found an old post here
http://www.mrexcel.com/forum/showthread.php?t=288378

But when going to the link and finding code it gives a compile error "user defined type not defined" on the first line

Dim VBProj As VBIDE.VBProject

Anyone have an idea?

Code

Sub AddProcedureToModule()
        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 = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub SayHello()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    
    End Sub