Before_Close Event Cleanup Limitation

[Jaafar Tribak]

You are familiar with this screen (except for the funny french letters ) It pops up on your screens each time you try to close an unsaved workbook which is great as it reminds you before you lose data.

So where is the problem ? The problem is that this screen pops up AFTER any potential cleanup code is executed in the BeforeClose event.

Let me explain why this can be problematic : Now what happens if the user clicks the Cancel button and decides to carry on working with the workbook ? The answer is that there will be many scenarios where the workbook will stop working properly .

One scenario is a running OnTime procedure that is being cancelled in the BeforeClose event. After the user clicks the Prompt Cancel button, the OnTime will no longer work !

I can think of many other scenarios where aborting the workbook closing process could cause problems later on if there is some cleanup code in the before_close event.

So here is the question: Is it possible to run code AFTER the save Prompt in order to overcome the problem explained above ? In other words : If the user clicks the Yes or No buttons then run the cleanup code but if the Cancel button is the one that's been clicked do nothing.

 

[mikerickson]

In your BeforeClose event you could do something like

If Not (ThisWorkbook.Saved) Then
    Select Case MsgBox("Workbook not saved. Should it be", vbYesNoCancel)
        Case vbYes
            Rem saving code
        Case vbNo
    
        Case vbCancel        
            Cancel = True
    End Select
    ThisWorkbook.Saved = True
End If

If Not Cancel Then
    Rem close-up code
End If

Basicaly, the code creates its own "this isn't saved" routine. Since it ThisWorkbook.Saved is always True after the routine, Excel's screen (shown in the OP) never shows up.

 

[ZVI]

The same as Mike’s code but a bit more detailed.

' Code of ThisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim ans
  With ThisWorkbook
    If Not .Saved Then
      ' Emulate "Save Changes" message
      ans = MsgBox("Save changes of " & .Name & "?", vbYesNoCancel)
      Select Case ans
      Case vbYes
        ' ... Call your code here...
        .Save
        ' ... or/and here ...
        .Saved = True
      Case vbNo
        ' Prevent "Save Changes" message appearing if Wb.Saved=False
        .Saved = True
      Case vbCancel
        Cancel = True
        ' Charge OnTime task
        Application.OnTime Now, .CodeName & ".Test1"
      End Select
    End If
  End With
End Sub

Sub Test1()
  MsgBox "Test1 code is running"
End Sub 

It's not the answer but seems it's workaround

 

[Jaafar Tribak]

Great stuff !

Nice use of the Saved Property . Mike's code fails if the user presses the cancel button twice . This slight modification solves the issue :

Case vbNo
          ThisWorkbook.Saved = True

Vladimir's code works flawlessly.

I had an idea in mind different from emulating the "Save Changes" excel built-in prompt by using a user defined Msgbox but this solution is much simpler

Thank you both Mike and Vladimir.

 

[shg]

ZVI,

What's the point of scheduling the Test1 macro instead of just calling it?

 

[ZVI]

ZVI,

What's the point of scheduling the Test1 macro instead of just calling it?

I was hypnotized by Jaafar’s mentioning about it
There is no special benefit in its usage here except of that OnTime runs with releasing memory after Workbook_BeforeClose is completely fulfilled.

 

[shg]

I was hypnotized by Jaafar’s mentioning about it

Fair enough

There is no special benefit in its usage here except of that OnTime runs with releasing memory after Workbook_BeforeClose is completely fulfilled.

Never gave that a thought; because VBA doesn't yield until the current code runs to completion?

Reckon that could be good or bad, depending; it incurs the expense of reopening the file.

Thanks.

 

[tusharm]

This has been a long standing issue with Excel VBA and monitoring the possible closing of the file.

The best solution to date has been the one already suggested. The key -- and some fail to recognize this -- is to not actually close the workbook. Excel is already in the process of doing so.

With Excel 2010, Microsoft introduced the AfterSave event. I have not explored it to see how it works, what its limitations, if any, are, or when the argument, named Success, is true and when it is false.

Great stuff !

Nice use of the Saved Property . Mike's code fails if the user presses the cancel button twice . This slight modification solves the issue :

Case vbNo
          ThisWorkbook.Saved = True

Vladimir's code works flawlessly.

I had an idea in mind different from emulating the "Save Changes" excel built-in prompt by using a user defined Msgbox but this solution is much simpler

Thank you both Mike and Vladimir.

 

[ZVI]

... because VBA doesn't yield until the current code runs to completion?

Depending on the code the more or less heap space of the memory is used by Excel & VBA object models during the macro running.
After running that space is released.

OnTime action waits till the shared and internal events of Excel are finished.
For example, without Cancel = True in my code, Excel will raise WorkbookBeforeClose event at application level and triggers the code if present.
Then runs Auto_Close code if present, shows "Save Changes" message if Wb.Saved=False, closes workbook if Cancel is not chosen, releases the used memory.
And only after that calls the Test1 with full path to it and as a result - with opening of workbook again.

OnTime method just charges the timer for callback of the macro, and it’s nearly the same as running that macro manually in the cleared memory space.

Without Cancel = True direct calling will also change the order of Test1 running relative to the described actions.
It’ll be the first one in this case.
But at Cancel = True your suggestion to call Test1 subroutine directly is more clear & preferable as for me.

So, thank you for calling me back from the hypnotized state!
But I'm feeling: Myself.Application Now, OnTime, "ToSleep"

 

[Jaafar Tribak]

Ok. Just for the sake of completeness and to show how we can indeed avoid the need to emulate the 'Save Changes' prompt and still be able to know what action the user took on the prompt and act accordingly here is what I 've come up with (On testing seemed to work smoothly) :

1- Plug this code in the Workbook module of a new workbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Monitor_Save_Changes = True

End Sub


'This Procedure MUST be Public
Public Sub MonitorProc(UserAction As Yes_No_Cancel)

    'Ignore all errors to avoid potential crashing.
    On Error Resume Next
    
    Select Case UserAction
        Case Is = Yes
            MsgBox "You Saved changes."
        Case Is = No
            MsgBox "You Discarded changes."
        Case Is = Cancel
            MsgBox "You Canceled Closing"
    End Select

End Sub

2- Place the following in a Standard module :

Option Explicit

Enum Yes_No_Cancel
    Yes = 6
    No = 7
    Cancel = 2
End Enum

Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal lNewLong As Long) As Long

Private Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long _
, ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" _
(ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) As Long
 
Private Declare Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, _
ByVal nCode As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
 
Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
 
Private Declare Function GetCurrentThreadId Lib "kernel32" _
() As Long
 
Private Declare Function GetClassName Lib "user32.dll" _
Alias "GetClassNameA" _
(ByVal hwnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long

Private Const WH_CBT = 5
Private Const GWL_HINSTANCE = (-6)
Private Const HCBT_CREATEWND = 3
Private Const GWL_WNDPROC = (-4)
Private Const WM_COMMAND = &H111
Private Const BN_CLICKED = 0

Private lCBTHook  As Long
Private lPrevWnProc As Long


'*******************
'Public Property.
'*******************
Public Property Let Monitor_Save_Changes(ByVal Status As Boolean)

    'Set a CBT hook to catch the 'Save Changes' Wnd creation.
    If Not ThisWorkbook.Saved And Status Then
        lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, _
                   GetAppInstance, GetCurrentThreadId)
    End If

End Property



'*******************
'Private Routines.
'*******************

Private Function CBTProc _
(ByVal idHook As Long, ByVal wParam As Long, _
 ByVal lParam As Long) As Long
 
    Dim sBuffer As String
    Dim lRetVal As Long
 
    Select Case idHook
        Case Is = HCBT_CREATEWND
            'Some Wnd has been created within the excel process.
            sBuffer = Space(256)
            lRetVal = GetClassName(wParam, sBuffer, 256)
            'Is it our 'SaveChanges' wnd ?
            If Left(sBuffer, lRetVal) = "#32770" Then
            'if so subclass it now.
            lPrevWnProc = SetWindowLong _
            (wParam, GWL_WNDPROC, AddressOf CallBack)
            End If
            'done with hook.
            UnhookWindowsHookEx lCBTHook
    End Select
    
    'Call next hook if any.
    CBTProc = CallNextHookEx _
    (lCBTHook, idHook, ByVal wParam, ByVal lParam)
 
End Function


Private Function CallBack _
(ByVal hwnd As Long, ByVal Msg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
    Dim LowWord As Long, HighWord As Long
    
    'Process the notifications sent by the buttons in the 'Save changes' Wnd.
    Select Case Msg
        Case WM_COMMAND
            Call SetWindowLong(hwnd, GWL_WNDPROC, lPrevWnProc)
            GetHiLoword wParam, LowWord, HighWord
            Call ThisWorkbook.MonitorProc(LowWord)
    End Select
    
    CallBack = CallWindowProc _
    (lPrevWnProc, hwnd, Msg, wParam, ByVal lParam)
 
End Function


Private Sub GetHiLoword _
(wParam As Long, ByRef loword As Long, ByRef hiword As Long)
 
   ' this is the LOWORD of the wParam:
    loword = wParam And &HFFFF&
    ' LOWORD now equals 65,535 or &HFFFF
    ' this is the HIWORD of the wParam:
    hiword = wParam \ &H10000 And &HFFFF&
    ' HIWORD now equals 30,583 or &H7777
 
End Sub

Private Function GetAppInstance() As Long
 
    GetAppInstance = GetWindowLong _
    (FindWindow("XLMAIN", Application.Caption), GWL_HINSTANCE)
 
End Function

Ok.This is clearly more involved than the provided solution so far and may well seem like overkill but I think it is cleaner than the solution provided so far as the bulk of the code is insulated in a seperate module and the BeforeClose event is left alone. I just hope this works consistently accross different platforms.