Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    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"
    End Sub

    Private Sub Workbook_BeforeClose()
    Workbooks("InvoiceList.xls").Close
    End Sub

    (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.
    Regards,
    Jim Wright.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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 !
    Regards,
    Jim Wright.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •