Here are three code fragments. The first one works propely, the second two
cause Excel to crash. Can someone tell me what I am doing wrong? Thanks

The following code works properly:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("ThisWorkbook ").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
"setLookupList" & Chr(13) & _
"End Sub"
End With

This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"'Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"'doIt Target" & Chr(13) & _
"End Sub"
End With

Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dotIt Target"
End With

At what point does Excel crash?

I've noticed the following points (don't know if they are causing the
problem though):
Your second example code has the single quote character before the Private
keyword and before doIt Target - is that intentional?

Your third example is calling dotIt rather than doIt

Is the doIt sub in a location that is within the scope of the
Worksheet_Change event? i.e. in same module or in a standard module

If the following code already exists in Sheet1's module:
Sub doIt(ByVal prngTarget As Range)

MsgBox "doIt sub: " & prngTarget.Address

End Sub

Then either/both of these work ok for me:
Sub TestVbe2()

Dim wb As String

wb = ThisWorkbook.Name
'This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"doIt Target" & Chr(13) & _
"End Sub"
End With
End Sub

Sub TestVbe3()

Dim wb As String

wb = ThisWorkbook.Name
'Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"doIt Target"
End With

End Sub
--
HTH

Simon


"keithb" wrote:

Here are three code fragments. The first one works propely, the second two
cause Excel to crash. Can someone tell me what I am doing wrong? Thanks

The following code works properly:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("ThisWorkbook ").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
"setLookupList" & Chr(13) & _
"End Sub"
End With

This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"'Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"'doIt Target" & Chr(13) & _
"End Sub"
End With

Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dotIt Target"
End With

With either approach, Excel crashes on execution of the .insertLines
command. The single quote characters and the dotIt rather than doIt are
entry errors in my email. I typed the information instead of cutting and
pasting from the code. I will play around with the working examples that you
send and let you know what I find.

Thanks for your help,

Keith

"Simon Letten" wrote in message
...
At what point does Excel crash?

I've noticed the following points (don't know if they are causing the
problem though):
Your second example code has the single quote character before the Private
keyword and before doIt Target - is that intentional?

Your third example is calling dotIt rather than doIt

Is the doIt sub in a location that is within the scope of the
Worksheet_Change event? i.e. in same module or in a standard module

If the following code already exists in Sheet1's module:
Sub doIt(ByVal prngTarget As Range)

MsgBox "doIt sub: " & prngTarget.Address

End Sub

Then either/both of these work ok for me:
Sub TestVbe2()

Dim wb As String

wb = ThisWorkbook.Name
'This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) & _
"doIt Target" & Chr(13) & _
"End Sub"
End With
End Sub

Sub TestVbe3()

Dim wb As String

wb = ThisWorkbook.Name
'Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"doIt Target"
End With

End Sub
--
HTH

Simon


"keithb" wrote:

Here are three code fragments. The first one works propely, the second
two
cause Excel to crash. Can someone tell me what I am doing wrong? Thanks

The following code works properly:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("ThisWorkbook ").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
"setLookupList" & Chr(13) & _
"End Sub"
End With

This code causes excel to crash:
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"'Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13)
& _
"'doIt Target" & Chr(13) & _
"End Sub"
End With

Similarly, this code also causes excel to crash:
Dim StartLine As Long
With Workbooks(wb).VBProject.VBComponents("Sheet1").Cod eModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dotIt Target"
End With