Something along the lines of
public Sub CreateEventProcedure()
Dim VBProj As Object 'VBIDE.VBProject
Dim VBComp As Object 'VBIDE.VBComponent
Dim CodeMod As Object 'VBIDE.CodeModule
Dim LineNum As Long
Const SHEET_NAME As String = "Sheet1"
Const EVENT_PROCEDURE As String = "Change"
Const SQL_CODE_LINE1 As String = "If Not Intersect(Target, Me.Range(""C8"")) Is Nothing Then "
Const SQL_CODE_LINE2 As String = ""
Const SQL_CODE_LINE3 As String = " MsgBox ""Hello World"""
Const SQL_CODE_LINe4 As String = "End If"
If VBAIsTrusted(ActiveWorkbook.VBProject) Then
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(SHEET_NAME)
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CreateEventProc(EVENT_PROCEDURE, "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, SQL_CODE_LINE1
LineNum = LineNum + 1
.InsertLines LineNum, SQL_CODE_LINE2
LineNum = LineNum + 1
.InsertLines LineNum, SQL_CODE_LINE3
LineNum = LineNum + 1
.InsertLines LineNum, SQL_CODE_LINe4
End With
End If
End Sub
'-----------------------------------------------------------------
Private Function VBAIsTrusted(ByRef Project As Object) As Boolean
'-----------------------------------------------------------------
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = Project.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function