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
--
Regards,
Tom Ogilvy
"C_Ascheman" wrote:
> 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
>
>
>
> "Dave Peterson" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >
|
Similar Threads
