+ Reply to Thread
Results 1 to 4 of 4

BeforeClose event not running/working when file is closed through VBA in another file

Hybrid View

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    BeforeClose event not running/working when file is closed through VBA in another file

    I have a code in file A that opens several files (B,C,D&E), copies some data from them, then closes the files.
    That part of the code works fine, but each of the files that are opened (B,C,D,&E) have a Workbook Open event that causes the file to save automatically every 30 seconds. (I know this is not recommended, but this is what the user wants.) The files also have a Workbook Before Close event that is supposed to stop the timer so the file will close without reopening. These each run fine on their own.
    But if I run code A, the workbook Before Close event in file B (C,D, & E) does not seem to run and the files reopen after 30 seconds to save. When I step through the code it works fine and goes through the Before Close event in each file and the files remain closed.

    I feel like there is something obvious I am missing. Any help is greatly appreciated. Thanks for taking the time to read my post.

    File A code:
    Sub CreateMasterLog()
    MSG1 = MsgBox("This will clear the current Pathology Log and replace it with the data on the current provider files.", vbYesNo, "Are you sure you want to continue?")
    If MSG1 = vbYes Then
    
    Sheets("Pathology Log").Range("A2:M2").End(xlDown).ClearContents
    Dim v As Workbook
    Dim w As Workbook
    Dim x As Workbook
    Dim y As Workbook
    Dim z As Workbook
    Dim varCellvalue As String
    Dim varCellvalue2 As String
    Dim varCellvalue3 As String
    Dim varCellvalue4 As String
    Dim varFilevalue As String
    Application.ScreenUpdating = False
    varFilevalue = Sheets("File Locations").Range("B2").Value
    varCellvalue = Sheets("File Locations").Range("B3").Value
    varCellvalue2 = Sheets("File Locations").Range("B4").Value
    varCellvalue3 = Sheets("File Locations").Range("B5").Value
    varCellvalue4 = Sheets("File Locations").Range("B6").Value
    
    '## Open all workbooks first:
    Set v = Workbooks.Open(varFilevalue & varCellvalue4)
    Set w = Workbooks.Open(varFilevalue & varCellvalue3)
    Set x = Workbooks.Open(varFilevalue & varCellvalue)
    Set y = ThisWorkbook
    Set z = Workbooks.Open(varFilevalue & varCellvalue2)
    
    'Now, copy what you want from x: and paste to y:
    Dim LastRow2 As Long
    LastRow2 = x.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow3 As Long
    LastRow3 = w.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow4 As Long
    LastRow4 = v.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow5 As Long
    LastRow5 = z.Worksheets("Pathology Log").UsedRange.Rows.Count
    
    x.Sheets("Pathology Log").Range("A2:M" & LastRow2).Copy
    y.Sheets("Pathology Log").Range("A2").PasteSpecial
    Application.CutCopyMode = False
    z.Sheets("Pathology Log").Range("A2:M" & LastRow5).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    w.Sheets("Pathology Log").Range("A2:M" & LastRow3).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    v.Sheets("Pathology Log").Range("A2:M" & LastRow4).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    
    'Close x & z:
    
    x.Close 
    z.Close 
    w.Close 
    v.Close 
    
    y.Sheets("Filter").Select
    
    End If
    End Sub
    File B, C, D, & E code posted in ThisWorkbook:
    Private Sub Workbook_Open()
        Call StartTimer
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call StopTimer
    End Sub
    File B, C, D, & E code posted in Module:
    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 30 ' 30 seconds
    Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
    
    Sub StartTimer()
        RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
            Schedule:=True
    End Sub
    
    Sub TheSub()
        ThisWorkbook.Save
        StartTimer  ' Reschedule the procedure
    End Sub
    
    Sub StopTimer()
        On Error Resume Next
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
            Schedule:=False
    End Sub
    Last edited by jaimelwilson; 06-26-2014 at 10:49 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,946

    Re: BeforeClose event not running/working when file is closed through VBA in another file

    Try putting Application.EnableEvents = False at the beginning of your code. And = True at the end.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: BeforeClose event not running/working when file is closed through VBA in another file

    Thank you so much!!! It now seems to work perfectly. I will post the final code below for anyone who may be interested.

    Sub CreateMasterLog()
    MSG1 = MsgBox("This will clear the current Pathology Log and replace it with the data on the current provider files.", vbYesNo, "Are you sure you want to continue?")
    If MSG1 = vbYes Then
    Application.EnableEvents = False
    Sheets("Pathology Log").Range("A2:M2").End(xlDown).ClearContents
    Dim v As Workbook
    Dim w As Workbook
    Dim x As Workbook
    Dim y As Workbook
    Dim z As Workbook
    Dim varCellvalue As String
    Dim varCellvalue2 As String
    Dim varCellvalue3 As String
    Dim varCellvalue4 As String
    Dim varFilevalue As String
    Application.ScreenUpdating = False
    varFilevalue = Sheets("File Locations").Range("B2").Value
    varCellvalue = Sheets("File Locations").Range("B3").Value
    varCellvalue2 = Sheets("File Locations").Range("B4").Value
    varCellvalue3 = Sheets("File Locations").Range("B5").Value
    varCellvalue4 = Sheets("File Locations").Range("B6").Value
    
    '## Open all workbooks first:
    Set v = Workbooks.Open(varFilevalue & varCellvalue4)
    Set w = Workbooks.Open(varFilevalue & varCellvalue3)
    Set x = Workbooks.Open(varFilevalue & varCellvalue)
    Set y = ThisWorkbook
    Set z = Workbooks.Open(varFilevalue & varCellvalue2)
    
    'Now, copy what you want from x: and paste to y:
    Dim LastRow2 As Long
    LastRow2 = x.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow3 As Long
    LastRow3 = w.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow4 As Long
    LastRow4 = v.Worksheets("Pathology Log").UsedRange.Rows.Count
    Dim LastRow5 As Long
    LastRow5 = z.Worksheets("Pathology Log").UsedRange.Rows.Count
    
    x.Sheets("Pathology Log").Range("A2:M" & LastRow2).Copy
    y.Sheets("Pathology Log").Range("A2").PasteSpecial
    Application.CutCopyMode = False
    z.Sheets("Pathology Log").Range("A2:M" & LastRow5).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    w.Sheets("Pathology Log").Range("A2:M" & LastRow3).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    v.Sheets("Pathology Log").Range("A2:M" & LastRow4).Copy
    y.Sheets("Pathology Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    
    'Close x & z:
    'Application.Wait (Now + TimeValue("0:00:10"))
    x.Close 'True 'SaveChanges:=False
    z.Close 'True 'SaveChanges:=False
    w.Close 'True 'SaveChanges:=False
    v.Close 'True 'SaveChanges:=False
    
    y.Sheets("Filter").Select
    Application.EnableEvents = True
    End If
    End Sub

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,946

    Re: BeforeClose event not running/working when file is closed through VBA in another file

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] strange sumifs problem, works with file open, #value with file closed
    By neowok in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2013, 05:32 AM
  2. Replies: 0
    Last Post: 01-27-2013, 12:13 PM
  3. Copy Data from Closed File to Existing File
    By srikanthbenoni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 08:36 AM
  4. Beforeclose problem when file is opened as Read only
    By Bluewhistler in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2010, 04:14 AM
  5. Replies: 2
    Last Post: 03-13-2005, 10:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1