Recording Macros in Excel through VB

[Vhati]

Hey,
I have a VB Application that creates an instance of an excel.application. The application then enters data in the excel sheet, just like normal. The thing that is tricky is i need to add code to a worksheet event, only i want this code to still be attached to the event after my VB application is closed. In other words, i need the code to be a macro in the workbook, not in my application
There is an application.RecordMacro methodb where you can pass in a string of VB code, but this requires the 'Record Macro' tool bar option to be recording to work. So the basis of my question is: is there anyway to start recording a macro through code, if not, how else could this be accomplished?

Any guidance on this issue woudl be amazingly helpful, as I've been stuck for a few days on it
Thank you!!

[D-niss]

This should put code in ThisWorkbook:

Sub addcode()
Dim Code As String
Dim nextline As Byte
 
Code = "Private Sub Workbook_Activate()" & vbCrLf
Code = Code & "   On error resume next" & vbCrLf
Code = Code & "   Sheets(""Sheet1"").Activate" & vbCrLf
Code = Code & "End sub"
 
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
    nextline = .CountOfLines + 1
    .InsertLines nextline, Code
End With
    
    
 
End Sub

Hope it helps.