Error causes Excel to shut down

[Guest]

Hello, I hope someone can help me with this..

I have a program (VBA in Excel XP with Win 2000) that does the following

1. Replace any existing sheet named "REPORT" with a clean on
2. Write a small VBA "BeforeDoubleClick" procedure into the sheet module of the NEW "Report" shee

The problem is, I can run this code fine from inside the VBE (i.e. if the new "report" sheet is already manually added), but when I run it as the user would from the spreadsheet, the program generates a fatal error ("Microsoft Excel has encountered a problem and wil be shut down") and the whole app closes.

The program runs fine up to the point at which the code is added to the sheet module. My code follows (NOTE: please correct for line wrapping in this text editor as applicable)

Dim objDestinationModule As Objec
Dim strSourceFile As Strin
Dim strDestinationModule As Strin
Dim strCodeLines As Strin

strSourceFile = ThisWorkbook.Nam
strDestinationModule = "REPORT

'Create an object reference to the VBA container object (REPORT sheet
Set objDestinationModule = Application.Workbooks(strSourceFile).VBProject.VBComponents.Item(Sheets(strDestinationModule).CodeName

'COPY DRILL DOWN CALLING CODE INTO A STRING
strCodeLines = "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & vbC
strCodeLines = strCodeLines & "If ActiveCell.Column > 3 And Cells(1, ActiveCell.Column).FormulaR1C1 <> " & """" & """" & " Then" & vbC
strCodeLines = strCodeLines & " Cancel = True" & vbC
strCodeLines = strCodeLines & " Call DrillDown" & vbC
strCodeLines = strCodeLines & "End If" & vbC
strCodeLines = strCodeLines & "End Sub" & vbC

'Append code into the destination modul
objDestinationModule.CodeModule.AddFromString strCodeLine

'************************************

As stated above: Each separate piece of code runs fine, combine them and ...kablu

Any assistance would be much appreciated to either debug my code or supply me with a rewrite all together

Thanks much in advance

 

[Guest]

After experimenting with this code further, I found out that if I run the program that writes code into the Sheet module at some point after the new "REPORT" sheet is created, it runs fine. Why? I don't know, just one of those things I guess

For anyone who is interested, my old code looked like

1. Replace any existing sheet named "REPORT" with a clean on
2. Write a small VBA "on event" program into the sheet module
3. Step 2 successful but fatal error resulted causing Excel to shut dow

Now my code looks something like

1. Replace any existing sheet named "REPORT" with a clean on
2. Do some formatting to the new shee
3. Do some other functions that add data to the new "REPORT" shee
4. Write a small VBA "on event" program into the sheet modul
5. Success - no error