Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot get Workbook_BeforeClose to run (Excel 2002 SP-1)

    I have the following in a workbook:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '
    ' Workbook_BeforeClose Macro
    ' Macro created 3/8/2004 by
    '

    '
    Application.ScreenUpdating = False
    ChDir "D:AccRptEmp"
    Workbooks.Open Filename:="D:AccRptEmp2003-2004OSHAForm300A.xls"
    Windows("2003-2004 OSHA 300.xls").Activate
    Sheets("OSHA Form 300A").Select
    Cells.Select
    Selection.Copy
    Windows("2003-2004OSHAForm300A.xls").Activate
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1:AE1").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.CutCopyMode = False
    Range("A1:AE1").Select
    Sheets("OSHA Form 300").Select
    Application.ScreenUpdating = True
    End Sub

    It does not run when the workbook is closed. I can run it manually when I use the same code in a macro so no errors that I can figure. I am choosing to enable macros when opening the workbook. I have even tried taking the security level to low to avoid being asked about macros and it still will not run.

    Any ideas??

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel 2002 SP-1)

    Where in the workbook is this code. Is it in the module behind the ThisWorkbook object? To be triggered by the close event, the code must be located in the ThisWorkbook object, it can not be in a normal module.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel

    In addition to Legare's advice, was your workbook originally created by XL 97?

    Some of my XL97 workbooks don't behave correctly in XL2000 but by recreating them in a nice clean brand new XL2000 workbook then all is well. I just put it down to old age.

    stuck

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel 2002 SP-1)

    It is listed as Module1 under Folder called Modules under an Icon called VBAProject (TheWorkbook).

    Is this where it needs to be? If not how do I move it?

    Thanks,

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel

    The workbook may have been originally created in an earlier version of Excel (In fact probably was.) but my work with the macro has all been done in the new version. The workbook is pretty extensive so would prefer not to have to rebuild if there is another way.

    Thanks,

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel 2002 SP-1)

    Highlight the Private Sub Workbook_BeforeClose(Cancel As Boolean) ... End Sub

    Edit - cut
    Open the "folder" named thisWOrkbook in the same project
    and dbl-click on it
    in its macro pane: edit -paste

    Now it is in the right place. It is a workbook event and needs to be in the workbook object. Code in modules run by being called by routines or avtively running them. Event triggered macros need to be in the object. SInce this is a WORKBOOK event it needs to be in "thisWOrkbook" not in a module

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot get Workbook_BeforeClose to run (Excel

    Screendump width reduced by HansV to avoid horizontal scrolling

    This screendump and Steve's instructions above should help you get the code into the correct place.

    stuck

Posting Permissions

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