Writing Workbook_BeforeSave into workbooks from an XLA

[Crookie74]

I have search around and found similar problems but not quite the same.


I am trying to put in Workbook_BeforeSave routines into the
"ThisWorkbook" module from a seperate XLA. The code works fine if i
write anything other than "Workbook_BeforeSave" but when I have it in,
it will write the code but then freezes up and then shuts down Excel. I
suspect Excel doesn't like it as Workbook_BeforeSave is a Excel defined
subroutine. Any help will be much appreciated. My (stripped down) code
is as follows:

Sub AddSaveAsFunction(ByVal wkbWorkbook As Workbook,
strBillingSheetName As String)
Dim strSheetCodeName As String
Dim VBThisWkbCodeMod As CodeModule
Dim LineNum As Long
Dim strWkbBeforeSave As String

strSheetCodeName = "ThisWorkbook"

strWkbBeforeSave = "Private Sub Workbook_BeforeSave(ByVal SaveAsUI
As Boolean, Cancel As Boolean)" & Chr(13) & _
vbTab & "Blah Blah" & Chr(13) & _
"End Sub"

Set VBThisWkbCodeMod =
wkbWorkbook.VBProject.VBComponents(strSheetCodeName).CodeModule
With VBThisWkbCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, strWkbBeforeSave
End With
End Sub