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?

:dunno

[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. :)