16 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. June: Why do you need to release the locks before close?

9. 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.

10. 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.

11. 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!

12. 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

13. 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.

14. 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.

15. One million times thank you!
    I’d been breaking my brain for three days literally, couldn’t switch to anithing until my problem is solved.
    Then I decided to go another way, but stumble at “cancel”, and here it is – your salvation!
    Wish you all the best!

16. I have asked a question on StackOverflow to improve this solution:

    https://stackoverflow.com/questions/58643395/how-to-detect-when-a-workbook-is-closing

Posting code? Use <pre> tags for VBA and <code> tags for inline.