Trying To Push Code For Worksheet_Change Into VBA-Created Workbook?

[(PeteCresswell)]

I can create a global module and populate it with various
routines, but I can't figure out the object hierarchy for
pushing in event code for a specific worksheet.

My playpen (in an MS Access application) includes this code,
which succeeds in pushing the desired event routine
into "Module1"... but it seems to me like I need it in
"ThisWorkbook".
-----------------------------------------------------------
Option Compare Database
Option Explicit

Const mModuleName As String = "basExcelCodePlaypen"
Private Declare Function apiSetForegroundWindow Lib "user32"
Alias "SetForegroundWindow" (ByVal hWnd As Long) As Long

Public Function bb()
2002 Dim mySS As Excel.Application
Dim myWS As Excel.Worksheet

Dim myWB As Excel.Workbook
Dim myCodeModule As CodeModule
Dim myParentModule As VBComponent

Dim myTargetPath As String

Const myCode As String = _
"Private Sub Worksheet_Change(ByVal Target As Range)" & vbLf & _
" If Target.Column = 1 Then" & vbLf & _
" Cells(Target.Row, 2).Value = Null" & vbLf & _
" End If" & vbLf & _
"End Sub"

' ---------------------------------------------------
' Create the empty .XLS document ,

2010 SpreadSheetOpen_New False, mySS
2019 mySS.ReferenceStyle = xlR1C1

' ---------------------------------------------------
' Create a workbook and the sheet we are going to
' populate

2020 With mySS
2021 .Workbooks.Add
2022 Set myWS = .Worksheets.Add
2023 End With
2029 myWS.Name = "BBB"


' ---------------------------------------------------
' ATTEMPT TO PUSH SOME EVENT CODE

2410 Set myWB = myWS.Parent
2420 Set myParentModule =
myWB.VBProject.VBComponents.Add(vbext_ct_StdModule)
2430 Set myCodeModule = myParentModule.CodeModule

2440 With myCodeModule
2441 .InsertLines .CountOfLines + 1, myCode
2449 End With

' -------------------------------------------------
' Save the spreadsheet with a time-stamped name

2910 myTargetPath = "C:\Temp\ExcelCodePlaypenOutput (" &
Format$(Now(), "yyyy mm-dd hh-nn-ss") & ").xls"
2919 mySS.Workbooks(1).SaveAs myTargetPath


' -------------------------------------------------
' Select the first sheet and make the .XLS visible to the user
' NB: We were having trouble with the focus returning to our
MsgBox prompt.
' Seems to be correlated w/putting that macro into the SS
' The API call is to work around that so the user can just
press
' Enter to dismiss the MsgBox instead of having to click
OK

2920 myWS.Select
2921 apiSetForegroundWindow hWnd:=Application.hWndAccessApp
2929 DoCmd.Hourglass False

2990 MsgBox "The spreadsheet is in '" & myTargetPath & "'.",
vbInformation, "Done!"
2991 mySS.Visible = True
2992 Set myCodeModule = Nothing
2993 Set myParentModule = Nothing
2994 Set myWS = Nothing
2995 Set myWB = Nothing
2999 Set mySS = Nothing

End Function

 

[(PeteCresswell)]

Per (PeteCresswell):

My playpen (in an MS Access application) includes this code,
which succeeds in pushing the desired event routine
into "Module1"... but it seems to me like I need it in
"ThisWorkbook".

I figured it out ("stumbled onto something that seems to work" is
probably a more accurate use of the language...).

Turns out that even though the sheet I'm creating is named "BBB"
by Yours Truly, it's known to the VBA code as "Sheet1" - even
after we have explicitly deleted "Sheet1".

viz:
===============================================================
' ---------------------------------------------------
' Create a workbook and the sheet we are going to
' populate

2020 With mySS
2021 .Workbooks.Add
2022 Set myWS = .Worksheets.Add
2023 End With
2029 myWS.Name = "BBB"

' ------------------------------------------------
' Remove any default sheets that the user's Excel
' defaults may have created

DoCmd.SetWarnings False
On Error Resume Next
With mySS
.Worksheets("Sheet1").Delete
.Worksheets("Sheet2").Delete
.Worksheets("Sheet3").Delete
End With
On Error GoTo 0
DoCmd.SetWarnings True

' ---------------------------------------------------
' ATTEMPT TO PUSH SOME EVENT CODE

2410 Set myWB = myWS.Parent
2420 Set myParentModule = myWB.VBProject.VBComponents("Sheet1")
2430 Set myCodeModule = myParentModule.CodeModule

2440 With myCodeModule
2441 .InsertLines .CountOfLines + 1, myCode
2449 End With
===============================================================