I'm attempting to use a Macro to create a workbook and add code to that
workbook.
This is the point in my code where I'm having trouble:

code = "private sub commandbutton1_click()" & vbCrLf & _
"Ans=MsgBox(" & Chr(34) & "Are you sure that you are ready to
send?" & Chr(34) & ", vbYesNo)" & vbCrLf & _
"if Ans=vbYes Then Thisworkbook.Route" & vbCrLf & _
"end sub"
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, code
End With

After this code runs, I get an application error stating that the memorry
cannot be "read", and Excel crashes. If I change "Sheet1" to "ThisWorkbook",
it runs fine. (I'm sure sheet1 exists.) Also, if, after Nextline, if I simply
type text (in quotes) and don't attempt to reference a variable containing
code, it runs fine. Is this an Excel bug? I'm using Excel XP on a Windows
2000 computer.

After examining things further, I noticed that if the code that I'm trying to
pass contains spaces, I get the error. However, if it is just something like
"ThisWorkbook.Route", I don't get the error.

"Jared" wrote:

I'm attempting to use a Macro to create a workbook and add code to that
workbook.
This is the point in my code where I'm having trouble:

code = "private sub commandbutton1_click()" & vbCrLf & _
"Ans=MsgBox(" & Chr(34) & "Are you sure that you are ready to
send?" & Chr(34) & ", vbYesNo)" & vbCrLf & _
"if Ans=vbYes Then Thisworkbook.Route" & vbCrLf & _
"end sub"
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, code
End With

After this code runs, I get an application error stating that the memorry
cannot be "read", and Excel crashes. If I change "Sheet1" to "ThisWorkbook",
it runs fine. (I'm sure sheet1 exists.) Also, if, after Nextline, if I simply
type text (in quotes) and don't attempt to reference a variable containing
code, it runs fine. Is this an Excel bug? I'm using Excel XP on a Windows
2000 computer.