Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file opened as read-only, if saved by another user | Excel Discussion (Misc queries) | |||
Possible to write VBA code to send out an EMAIL everytime a workbook is opened? | Excel Programming | |||
How to find out the residing directory of the opened workbook using VBA code ? | Excel Programming | |||
Code to check if workbook is the last one opened | Excel Programming | |||
Code to tell if workbook is opened in browser | Excel Programming |