Reset Sub Workbook_BeforeClose

[Nina97]

Hello everybody,

I have a question regarding the BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Goodbye"
End Sub

So I click on the exit button (x top right corner) and the text message is shown, but if I answer the "Do you want to save?"-box with "Abort" and then want to exit excel again, the "Goodbye"-message is not shown anymore?

How am I able to reset the BeforeClose sub so it will fire everytime I want to exit excel via X-button?


Kind regards.

[Nina97]

Oh, I guess I figured it out.

I have to use a module instead of the workbook sheet

Thanks anyway and have a good night

[Nina97]

Hmm, unfortunately I smiled way to soon.

I used Sub Auto_Close in the module instead of WorkBook_BeforeClose and WorkBook_BeforeClose doesn't seem to work the same way and only triggers once per session for me, so my question is still open.

How to have the msgbox appear every time I click on the exit button, even after choosing to "Abort" the "Do you want to save"-question?

[SamT]

I think you need to use the "Cancel" Parameter.

Maybe something like this

Dim CancelCloseBook As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
'   Your Code here
'   Your code sets CancelBookCloed to True if you want 
'  Workbook_BeforeClose to still work

Cancel = CancelBookClosed
End Sub

[Nina97]

Hey, thank you very much for your response!

I have a few more informations on the problem now.

I am at my home computer (Excel 2016) and this is the code I am using:

Option Explicit


Private Sub Workbook_Open()

    varPrintDone = False

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim lastRowShipping As Long

    With Sheets("Shipping")
        lastRowShipping = IIf(IsEmpty(.Cells(.Rows.Count, 2)), .Cells(.Rows.Count, 2).End(xlUp).Row, .Rows.Count)
    End With

    If varPrintDone = False Then
        If lastRowShipping = 6 Then
            Application.DisplayAlerts = False
            Application.Quit
            Application.DisplayAlerts = True
        ElseIf lastRowShipping <> 6 Then
            noPrintDoneForm.Show
            Cancel = True
        End If
    End If
End Sub

And everything works, but Workbook_BeforeClose only fires once a session.

Now, today I tested this exact same code (same file) on another computer (Excel 2010) and it does work just the way I want it to work, every single time I press the eXit-Button, when varPrintDone = false and lastRowShipping <> 6, my UserForm (noPrintDoneForm) pops up.

What could be the root of the problem at my home computer?

The only two (to me known) differences are the Excel Version (2016 - 2010) and that on my computer, when I open more than one Excel file I do have them seperately shown on my desktop/screen (can move both of them on my desktop, for example put them next to each other and watch both of them at the same time), while on the other computer, when you open more than one file, there is only one file showing up. When you switch between the files the other ones aren't shown.

Uh, sorry for my English, I hope it is understandable.

So the code does work, but not at my home computer.

Kind regards.