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.

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.

Hi guys,

Thanks for the prompt replies.
I guess I can stop worrying about it now.

Unless of course you'd like to point me in the right direction to get
round this.

Cheers,
JF.

Alternative:
instead of dumping a lot of code in workbooks
that are essentially supposed to contain data

my approach would (probably) be to create an addin with
an application level event handler.


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


wrote :

Hi guys,

Thanks for the prompt replies.
I guess I can stop worrying about it now.

Unless of course you'd like to point me in the right direction to get
round this.

Cheers,
JF.

Check if you havn't saved the macro to Personal.xls which stays hidden.
You can un-hide it Go to Window Unhide (if not greyed out)

When recording a macro you have the 'choice' of saving your code to 'This
Workbook' (ie current open spreadsheet) or to the 'Personal Macro Workbook'
Check the first 'form' after stating a new macro but before the macro
recorder is turned on.
;)
Peter


"Joshua Fandango" wrote in message
om...
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.