Joshua

could it be that you have McAfee virus scanner running?

If McAfee finds vba code the calls or modifies vbcomponents from
thisworkbook module is wipes the codemodule.

Check your virus logs.

hth!


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Joshua Fandango wrote :

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.