Unanswered: Capturing Open and Close events in Excel.
Hi, colleagues all,
The functionality I wish to add to a series of workbooks is for each one (the master) to open another workbook in the background, update the background workbook with data from the master and then close the background workbook when the master closes.
MS Excel Help tells me that at the Application level, one must create and Application object with the With Events directive and then link it to the current application, but that this is not necessary with Workbook and Worksheet events.
The VBA code that I have added to Module 1 of each workbook is as follows:
Global ThisWorkBook As String
Private Sub Workbook_Open()
ThisWorkBook = Workbooks(1).Name
' MsgBox ThisWorkBook
Workbooks.Open Filename:="E:\Data\Dunelm\Projects\m_fust\InvoiceL ist.xls"
' MsgBox "InvoiceList opened"
Private Sub Workbook_BeforeClose()
(The Global bits are used by the updating functions in Personal.xls).
My problem is that this code is not run when I open any of the workbooks. I have turned the macro security down to its lowest setting and turned off all protection, but without success. I bet I am missing something absurdly simple here, but I am more au fait with Access and larger systems. I promise not to hate anyone who can highlight my shortcomings in this area.
This code does not go in the normal module, it is workbook level code. Rather, in Project Explorer in VBE, on the left window you will see the project and the various worksheets listed plus an icon for the workbook itself. Double-click the workbook icon for this project, then in the window on the right paste your code.
Last edited by shades; 05-06-06 at 10:44.
old, slow, and confused
but at least I'm inconsistent!
Thanks a million, Rich. It works perfectly. It just goes to show that one must read Microsoft books very carefully to get the instructive stuff out from behind the descriptions of the "really cool" features !