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("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. |
Excel vba code disappears when saved workbook is re opened
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. |
Excel vba code disappears when saved workbook is re opened
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. |
Excel vba code disappears when saved workbook is re opened
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. |
Excel vba code disappears when saved workbook is re opened
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. |
All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com