Is there any way to trigger event before excel application close?

[vonguyenphu]

I know the event before workbook close but this won't work if I close the whole application (eg: via press "Alt+F4" on windows). How can I trigger event before application close?

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Const sBak        As String = "C:\Users\vnphu\OneDrive\Personal Workbook Backup\PERSONAL.XLSB."

  Application.DisplayAlerts = False
  With Workbooks("PERSONAL.XLSB")
    .SaveCopyAs sBak & Format(Now(), "yyyy-mmdd-hhmm.bak")
    .Save
  End With
  Application.DisplayAlerts = True
End Sub

 

[iliace]

There is no application-level event for application close, so that won't help you. The application-level workbook close events seems to exhibit the same behavior as the workbook-level event.

I tried this a few different ways, and it looks like Excel will not run the workbook_beforeclose event if you have a hidden file called PERSONAL.XLSB. If you call it anything other than PERSONAL.XLSB (for example, I have Macros.xlsm) the code works fine. Also, if PERSONAL.XLSB is unhidden, it runs the event code as expected.

However, it looks like you can trick Excel into doing what you want, since you don't need the "Cancel" parameter. Insert a standard module, and use auto_close instead of workbook_beforeclose. Try this code (using a standard module, not a workbook/class module):

Private Sub auto_close()
  Const sBak  As String = "C:\Users\vnphu\OneDrive\Personal Workbook Backup\PERSONAL.XLSB."
  Application.DisplayAlerts = False
  With Workbooks("PERSONAL.XLSB")
    .SaveCopyAs sBak & VBA.Format(Now(), "yyyy-mmdd-hhmm") & ".xlsb"
    .Save
  End With
  Application.DisplayAlerts = True
End Sub

 

[vonguyenphu]

Hi iliace, your solution works as I expect. Thank you
A notice here is the Workbook_BeforeClose event doesn't work even If I unhide PERSONAL.XLSB.

 

[iliace]

Based on my testing, I don't think this is true. I am using Office 365 to test, and possibly there is disagreement between Office versions.

Can you add this line to your code:

  Call MsgBox("Closing!", Title:=ThisWorkbook.Name)

and see if a message box pops up when you try to save?

 

[iliace]

In general, the troubleshooting procedure should include the following steps:
* does this problem occur when I use it on my main PERSONAL.XLSB workbook
* does it make a difference, using the exact same parameters, when PERSONAL.XLSB workbook is unhidden
* if I save PERSONAL.XLSB workbook in a location other than XLSTART, does it make a difference
* in this other location, does it make a difference whether PERSONAL.XLSB is hidden or not
* if I call PERSONAL.XLSB something else in the XLSTART location, does that make a difference
* I save another workbook in XLSTART location, does it behave the same way as PERSONAL.XLSB?
* the new XLSTART workbook is hidden, does it behave the same way as PERSONAL.XLSB?
* I change the name of PERSONAL.XLSB to PersonalMacros.xlsm, and save it in XLSTART, does it behave the same way as PERSONAL.XLSB?
* PersonalMacros.xlsm is saved in another folder, does it still behave the same way?
* .....and so on

You have to do some of your own troubleshooting, based on how your system (and possibly admin permissions) are set up.

 

[vonguyenphu]

The code runs on .xlsm files but does not run on .xlsb files after I tested. You really taught me more than I expect, thank you again sir.