Hi guys & gals,

Funny one this.

Using an amalgam of 2 codes I found on the ever helpful Chip Pearson's
website
I (finally) figured out how to write to the 'ThisWorkbook' module of a
VBA project.
All good so far...

Until, that is, when I save the workbook the code resides in and
re-open it said code has disappeared! If there is other code present
in the same standard module then only the code goes - if it resides in
a module on it's own, then the module disappears.

As I'm sure you can imagine this is a little annoying and I'm not
sufficiently gifted to hazard a guess as to what is going on as there
seems to be nothing about the code itself that would make this happen.

It has been a good learning experience though as I had to re-write it
twice before I saved it elsewhere and managed to knock it down by a
few lines each time.

Code follows.....

Sub Auto_Write_To_ThisWorkbook()

Dim StartLine As Long
Dim LineNum As Long

''Inserts BeforeClose event in ThisWorkbook module of the active
workbook

'With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
' StartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
'End With

'Inserts BeforeClose event in ThisWorkbook module of the active
workbook
'and adds the body of the text of the procedure to the event

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("BeforeClose", "Workbook") + 2
LineNum = 3
.InsertLines LineNum, _
"Ans = MsgBox(""Don't forget to run the 'Update Lookup Tables'"" &
vbCr & ""procedure once all comments are updated"" & vbCr & ""Would
you like to open this now?"", vbYesNo, ""Information"")" & Chr(13) & _
"" & Chr(13) & _
"If Ans = vbYes Then" & Chr(13) & _
" Workbooks.Open Filename:= _" & Chr(13) & _
" ""Z:\TRADE FINANCE\Shared Area\Within Trade
Finance\SUSPENCE ACCOUNT\Suspence Queries\UPDATE LOOKUP TABLES.xls"""
& Chr(13) & _
"Else" & Chr(13) & _
" If Ans = vbNo Then" & Chr(13) & _
" Exit Sub" & Chr(13) & _
" End If" & Chr(13) & _
"End If" & Chr(13) & _
""

End With

End Sub

Any help/comments on this would be much appriciated.

Cheers,
JF.