Closing Form and prevention

[C_Ascheman]

Here is the code I have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
MsgBox "You have clicked the X in the upper right corner!"
MsgBox "This will close the Excel Program, and all subsequent workbooks
opened within."
MsgBox "If this was an accident, or you do not want to close all the
workbooks then click on the CANCEL button."
MsgBox "Otherwise click the OK button, and everything will be closed.",
vbOKCancel
If vbOK = 1 Then Exit Sub
If vbOK = 0 Then
Application.Quit
End If
End Sub

This partially does what I am wanting it to do. The purpose of this is
if one of the users has multiple workbooks open, and accidentally hits one of
the x's in the corner that it gives them a message of what they did. Then
Depending whether they hit cancel or ok decides the outcome. If they didn't
mean to hit the x, and want to keep the form open then they hit the cancel
button. If they do want to close all of it down then they hit ok. The ok part
works as intended obviously. The problem is if they hit the cancel button
they get the save dialog box. If they hit yes they save it. If they hit no
even though they don't want to close the application it does anyway. With
cancel all remains open.
Is there a way to prevent the file save dialog from popping up, and to
prevent the application from closing after they hit the cancel button?

Thanks,

C_Ascheman

 

[Dave Peterson]

This kind of code is related to a single workbook. It'll fire whenever the user
is closing that workbook--not just when the user clicks on the X to close the
application.

I don't think that there's anything you can do that only fires when the user
clicks on the X to close the application.

 

[C_Ascheman]

Any suggestions then. Personally I don't think it needs in there, but my boss
disagrees as he has accidentally closed down multiple workbooks by
accidentally clicking the x of the excel window. I will be putting this code
into all the Excel programs we use, which will be a nuisance, but I don't
have much of an option there. Got to do what the boss wants. If you have any
suggestions to help me achieve what I am trying to do then please help.

Thanks,

C_Ascheman

 

[Tom Ogilvy]

Leave it in there. Everytime they try to close a workbook, they will get the
prompt. After a while, they will ask you to take it out.

You had some errors in your code.

You test
if vbOK = 1 then

vbOK will always equal 1 - it is a constant. YOu want to examine the
response the user has given.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
MsgBox "You have clicked the X in the upper right corner!"
MsgBox "This will close the Excel Program, and all subsequent workbooks
opened within."
MsgBox "If this was an accident, or you do not want to close all the
workbooks then click on the CANCEL button."
ans = MsgBox( "Otherwise click the OK button, and everything will be
closed.",
vbOKCancel)
If ans = vbOK Then
Exit Sub
else
cancel = True
End If
End Sub