BeforeClose vs. BeforeReallyClose

The Workbook_BeforeClose event fires when a workbook is closed. But just like the name says, it actually fires just before the workbook is closed – or so you thought. The problem with the BeforeClose event is that it can run even if the workbook is not eventaully closed.

If the workbook is unsaved and the user attempts to close it, the BeforeClose event procedure will run. The user will be presented with a message box asking if he wants to save changes. One of the choices on the message box is Cancel. The problem occurs when the user cancels the close, but the BeforeClose has already run.

To get around this problem, you can use the Workbook_Deactivate event. But that event needs a little help. First, in a standard module, create two variables; a Boolean to determine if the workbook is closing and a Date to schedule a macro to run later.

Public gbClosing As Boolean
Public gdCheckTime As Date

In this example, the problem will be that we create a commandbar in the Open event and destroy it in the BeforeClose event. If the user cancels the close, the commandbar will still be deleted. In the ThisWorkbook module the Open event creates the commandbar

Private Sub Workbook_Open()

    Dim cb As CommandBar
    Dim cbb As CommandBarButton
   
    On Error Resume Next
        Application.CommandBars(“MyBar”).Delete
    On Error GoTo 0
   
    Set cb = Application.CommandBars.Add(“MyBar”)
   
    Set cbb = cb.Controls.Add(msoControlButton)
    cbb.Caption = “Tester”
    cbb.Style = msoButtonCaption
       
    cb.Visible = True
   
End Sub

Next we’ll destroy the commandbar in the Deactivate event, but condition it on the gbClosing variable. We’ll also cancel the scheduled macro (scheduled in the BeforeClose event – read on).

Private Sub Workbook_Deactivate()

    If gbClosing Then
        On Error Resume Next
            Application.CommandBars(“MyBar”).Delete
        On Error GoTo 0
        ‘Delete scheduled macro
       Application.OnTime gdCheckTime, “CheckIfClosed”, , False
    End If
   
End Sub

In the BeforeClose event, the global variable is changed to True to indicate to the Deactivate event that a close is happening and not that the user is just switching workbooks (which also fires the Deactivate event). Also, a macro is schedule to reset the global variable to False.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    gbClosing = True
    gdCheckTime = Now + TimeSerial(0, 0, 1) ‘Define time
   Application.OnTime gdCheckTime, “CheckIfClosed” ‘Schedule macro
   
End Sub

Finally, back in the standard module, create the sub that resets the global variable.

Sub CheckIfClosed()
   
    ‘If workbook has not been deactivated, this macro
   ‘will run and reset the global variable.
   gbClosing = False
   
End Sub

Okay, this is how it’s supposed to work: The user closes an unsaved workbook. The BeforeClose event sets the gbClosing flag to True and schedules a procedure to set it back to False. If the close is cancelled, the Deactive event never runs (and the toolbar isn’t deleted) and the scheduled macro resets the flag. If the close isn’t cancelled, the workbook is closed and the Deactivate event does it’s thing.

I hope that’s how it actually works, but let me know if you see a problem with it.

Posted in Uncategorized

14 thoughts on “BeforeClose vs. BeforeReallyClose

  1. I like your approach and I have adopted it in my Excel sheets. One problem I have with both your approach and the one I used prior, If I open up two different spreadsheets with this code and then close one, it still deletes the commandbar. Perhaps another global variable that indicates that multiple workbooks are open would allow the last one closing to delete the commandbar. I did add code to make sure the bar didn’t already exist since I didn’t want to create a duplicate bar in that case.

    The code I use in the “Workbook_Open” event for that is:

    Dim tmpBar As CommandBar
    For Each tmpBar In Application.CommandBars
    If tmpBar.Name = “MyBar” Then
    Exit Sub
    End If
    Next

  2. Hey, I was wondering if you could tell me how I autofill the save filename. What I mean is that, I would like everytime someone closes the excel sheet I created, that the default save name to be whatever is in cell A1. They would still have the option of changing it, and office would still warn them that they same name exists to they want to override, but I would like the name that appears automatically to be linked to a cell (in this case A1).

    Thanks,

    Dan

  3. Put the following code into the BeforeSave event of the ThisWorkbook module.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strSaveAs As String

    strSaveAs = ActiveSheet.Range(“A1?)

    If SaveAsUI Then

    Cancel = True
    strSaveAs = Application.GetSaveAsFilename(strSaveAs, “Microsoft Office Excel Workbook (*.xls), *.xls”)
    If Not strSaveAs = “False” Then ActiveWorkbook.SaveAs Filename:=strSaveAs

    End If

    End Sub

    Type something into Range(“A1?) of the activesheet and when you try to saveas the workbook the contents of the cell should appear in the SaveAs dialog.

    This example will only let you save your file as .xls and also doesn’t allow you to set any passwords when you save your file.

    Hope this is what you were looking for.

    John

  4. I am trying to implement beforebooksave, but MS window said that it encounters a problem and forces the excel window to close.

    Below is my code for beforebooksave event:

    Private eventDel_BeforeBookSave As Excel.AppEvents_WorkbookBeforeSaveEventHandler

    eventDel_BeforeBookSave = New Excel.AppEvents_WorkbookBeforeSaveEventHandler(AddressOf BeforeBooksave)

    AddHandler objExcel.WorkbookBeforeSave, eventDel_BeforeBookSave

    Private Sub BeforeBooksave(ByVal wb As Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef cancel As Boolean)
    wb.SaveAs(“C:Junkcoba2.xls”)
    End Sub

    I debugged it, and what happened was it went thru the event handler twice automatically. Do you have any suggestion what might go wrong?
    Thank you in advanced?

  5. Hi,

    could anybody please help me to understand this tutorial. I don’t get the part where the onTime function calls ‘CheckIfClosed’ and sets the value off gbClosing to false. Does this also work without the the ‘CheckIfClosed’ function? Or shouldn’t the gbClosing be set to true in the function ‘CheckIfClosed’ to make this work?

    Please help me to understand it better, I need this to solve exactly the same problem with a selfmade commandbar.

    Thanks,
    Kristin

  6. (I sent this once, it’s in limbo I guess, Dick please delete one if it shows up twice)

    Kristin,

    If your issue has to do with just creating and deleting the commandbar at the correct time, try just using the activate and deactivate events. They fire when the workbook opens and closes, respectively, and deactivate avoids the problem Dick mentions above. If, on the other hand, you need to distinguish between actions that happen at close and those that happen at deactivate, you need to do something like above.

    If you do have an event that you want to only happen at close, you should do like Dick suggests above. I don’t use the OnTime method, I just set the gbClosing in the Deactivate and BeforeClose events.

    Here’s what I’d do to cover both the menu creation/deletion on Activate/Deactivate and what I’ll call the ReallyClose event. This is all in the ThisWorkbook module:

    Public gbClosing As Boolean

    Private Sub Workbook_Activate()
    Dim cb As CommandBar
    Dim cbb As CommandBarButton

    On Error Resume Next
    Application.CommandBars(“MyBar”).Delete
    On Error GoTo 0
    Set cb = Application.CommandBars.Add(“MyBar”)
    Set cbb = cb.Controls.Add(msoControlButton)
    cbb.Caption = “Tester”
    cbb.Style = msoButtonCaption
    cb.Visible = True
    End Sub

    Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.CommandBars(“MyBar”).Delete
    MsgBox “Deactivate”
    On Error GoTo 0
    If gbClosing Then
    MsgBox “ReallyClose”
    End If
    gbClosing = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    gbClosing = True
    End Sub

    You should know that I have bad luck in trying to improve on Dick’s code – I’m usually wrong :). But try for yourself and see what you think.

  7. Dick,

    For my case, some cells are locked & protected during open. And I need to release the locks and protection before close the file.

    My issue is if the user close the worksheet but not save, how can I save my lock / protection release setup information.

    Please help. Thanks.

  8. Responding to the original post…Might it not be easier to check if the workbook is dirty and ask the user if they want to save the WB? Then, if the answer is yes and the WB has never been saved proceed to the GetFileSaveAs dialog box.

    And, no, it would not be OK — as some klutz in some other discussion wrote s/he would do — to simply set the WB’s Saved property to True, which I imagine would mean Excel would not ask the user if the WB should be saved. There are any number of times when I close a document expecting the program to ask me if it should be saved.

    Of course, this presumes that it is OK to programmatically save the file after Excel has initiated the close process.

  9. Release locks before close & unprotect the worksheets because there are data exchange between the workbooks by VBA. The VBA can’t work for protected worksheets. Please help. Thanks.

  10. You can release the locks before close using the following subs that I wrote: UnprotectSh and Unlocks_All in your Workbook_BeforeClose

    Sub UnprotectSh()
    ‘make sure only one sheet is selected before running this sub or it fails
    Sheets(“insert the name of one of your sheets here…”).Select

    Dim sheetCount As Integer
    Dim i As Integer

    ‘count how many sheets there are in the workbook
    sheetCount = Worksheets.Count

    ‘unprotect each sheet number until the sheetCount is reached
    For i = 1 To sheetCount
    Sheets(i).Unprotect
    Next i

    End Sub

    Sub Unlocks_All()

    ‘ Unlocks everything, be careful using this..
    ‘ You must run the UnprotectSh sub first so that all of the sheets can be selected

    ‘select every cell on every sheet and substitute these sheet names with yours,
    ‘recording a macro and selecting all of the sheets is the easiest way..
    Sheets(Array(“Year to Date Summary”, “January”, “February”, “March”, “April”, “May”, _
    “June”, “July”, “August”, “September”, “October”, “November”, “December”)).Select
    Sheets(“Year to Date Summary”).Activate
    Cells.Select
    Range(“A1?).Activate

    ‘unlocks the selected cells and ensure that formulas *can* be seen
    Selection.Locked = False
    Selection.FormulaHidden = False

    ‘select only one cell so that it’s neater, again, substitute your values
    Sheets(“Year to Date Summary”).Select
    Range(“A1?).Select

    End Sub

    Remember to lock everything down again when on Workbook_Open, I use a Locks_All and ProtectSh in that order and they’re basically the reverse of the unlock and unprotect. Good luck!

  11. I’m glad this came up, because this is something I’ve wanted to do. And now that I’ve done it (Dick’s code–no surprise here–works) I find that I don’t quite understand it.

    I’m staring at the “Save changes” query. I take that to mean (after some experimentation) that Workbook_BeforeClose has run. And that Dick’s one second delay has timed out. So both Workbook_Deactivate and CheckifClosed are pending. Since the code does work, that means Workbook_Deactivate has to go next if I choose anything but Cancel. Why?? Well, because, of course. Where is the pecking order of macros highlighted? Checking Chip Pearson’s Event Sequence, standard module macros (or at least Auto_close does) run before Workbook_deactivate, meaning CheckifClosed should do its thing (which as I said, it doesn’t).

    Can anybody help me fully understand the flow when Cancel isn’t the choice? Thanks.

    …Michael

  12. Thank you for this clever and useful workaround. It is annoying that it is necessary, BeforeClose should mean that. I found that with multiple workbooks open I needed to add Application.Quit in the BeforeClose sub, otherwise I had to close Excel twice.

  13. This is great, except for when the file being saved is a large file being uploaded to a server. In this case, it might be a good idea to push the checkifclosed sub out a bit.

Leave a Reply

Your email address will not be published. Required fields are marked *