I've been trying for a few days to add code to a workbook created
"on-the-fly" The workbook also has controls added "on-the-fly" so I need to
add the code to support the controls.

Have had a lot of difficulty but I believe I've come up w/ a solution:
I've added a commented line of dashes into the CodeModule in one sub and
then added the remaining code into the CodeModule in a separate sub.

I think the compiler generates a compile request when the code is all added
at once and the code preceeds the Option Explicit line in the declaration
section causing my Excel to crash.

This same code is added to each of 4 worksheet codemodules


Here's the example:

Sub 1

With objProject.VBComponents(MyWkSht.CodeName).CodeModu le
.InsertLines Line:=1, String:="'-----------------------------------------"
End With

Sub 2

With objProject.VBComponents(MyWkSht.CodeName).CodeModu le

..InsertLines Line:=.CountOfLines + 1, String:="Dim blnAnswer2 as Boolean" &
vbCr & _
"Dim strPrompt2 as String" & vbCr & _
"'-------------------------"
.InsertLines Line:=.CountOfLines + 1, String:="Sub chkHideAll_Click" & vbCr
& _
" strPrompt2 = ""This is My Test""" & vbCr & _
" blnAnswer2 = MsgBox(strPrompt2, vbokonly)" & vbCr & _

"End Sub"

The CountOfLines definitely gets the code after the Option Explicit
statement and it has worked a number of times in a row - no crashes.

Good luck, let me know if you have a problem, I've not tested this
extensively.

"Alex T" wrote:

Folks

Is this also crashing your Excel ?! If so anything obvious I might have missed ?

----

Sub doStuff()

Dim x As OLEObject
Dim aName As String

For Each x In ActiveSheet.OLEObjects

aName = x.Name

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
.InsertLines .CreateEventProc("Click", aName) + 1, _
"Msgbox ""Hi there"" "
End With

Next x

End Sub