Excel vba code disappears when saved workbook is re opened

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("ThisWorkbook").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("ThisWorkbook").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("ThisWorkbook").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("ThisWorkbook").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.

keepITcool shared this with us in microsoft.public.excel.programming:

> 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.

(Hey Jurgen aka keepITcool how are you?)

I can confirm this problem with McAfee.
I had exactly the same problem at work, with the managed virusscanner
McAfee ASaP.
Heh. I even used the same code example from the same book - as
keepITcool might remember from a few months ago in
news:microsoft.public.nl.office.excel

To be more precise: any method that only READS from the vbcomponents
should be fine.
Any method that WRITES (like InsertLines) causes McAfee to see this as
a possible unknown macro virus and it then wipes the entire code
module. Presumably real macro viruses also work this way.

I found no solution for this problem, because there was no way to tell
the managed network-wide virus scanner to make an exception.
The only thing I could do was to rewrite everything from scratch
without vbcomponents, with a whole new concept. The tradeoff is that it
is now not as fast, elegant and flexible as my first solution with
vbcomponents.

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way
http://www.catb.org/~esr/faqs/smart-questions.html
How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoyn...-with-yes-or-n
o-answers.html

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


[email protected] 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" <[email protected]> wrote in message
news:[email protected]...
> 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("ThisWorkbook").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("ThisWorkbook").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.