vba workbooks before close skipping over open command

Viewed 737

I have three workbooks, a parent book, a library book and a log book.

The parent book opens a library, and then closes the library. Coincidentally the library has a provision in the workbooks on open and before close events to write to a log book whenever it is opened or closed.

My issue is that when the library is opened programmatically from the parent book, the code to open the log book before close is skipped over. I get no errors, it just simply evaluates the line and produces no result.

CODE:

From my parent book I run this line:

...
Workbooks(parts_library_name).Close savechanges:=True
...

Which runs this code in the library book:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set libwkb = ThisWorkbook
'last call to backup usage sheet
backup_exit_size
'exit
End Sub

Sub backup_exit_size()
Dim bypass_close_usage_library As Boolean

Application.DisplayAlerts = False
file_size_at_close = FileLen(ThisWorkbook.Path & "\" & ThisWorkbook.Name)
For Each Workbook In Workbooks
    If Workbook.Name = "usage_library.xlsx" Then bypass_close_usage_library = True
Next
Application.ScreenUpdating = False
If bypass_close_usage_library = False Then
    Workbooks.Open "somepath\usage_library.xlsx"
End If
Workbooks("usage_library.xlsx").Worksheets("usage").Cells(next_row_usage_library, 3) = file_size_at_close
Workbooks("usage_library.xlsx").Close savechanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

The problem is that when it gets to this line:

    Workbooks.Open "somepath\usage_library.xlsx"

the compiler simply skips over the command.

That is only the case however if I get to the beforeClose event that originated from another Book. If I close the book from itself, the macro runs fine and opens the log book fine.

I have tried a with statement from the parent book, like

With library.xlsx
 .RunAutoMacros xlAutoClose 
 .Close 
End With

but have the same results, it evaluates the line and then skips over it.

Thoughts?

3 Answers
Related