Application error when attempting to write code to a workbook

J

Jared

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").CodeModule
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.
 
J

Jared

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top