Workbook_BeforeClose Event and Cancel Option
 

The following below displays a message when a workbook is closed. However if
the user hits the cancel button when prompted with the options prior to
close, this code still creates the message. This is becuase this event is
triggered before the user is presented with the close options. How do you
get around this. Is there another event that I can use to verify if in fact
the file has been closed.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Cancel = False Then
Msgbox("Book closed")
End If

End Sub

Workbook_BeforeClose Event and Cancel Option
 

Yes of course they will, you need to substitute one of the message boxes
with your desired action, to cancel the operation or show the workbook
as Saved or save the workbook.

Excel Monkey;309492 Wrote:
This works but the user will then be prompted again with the Windows
message
box as well.

Thanks

EM

"Simon Lloyd" wrote:


Perhaps, rather than that statement maybe capture the cancel from
your
on message box something like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyBox
Cancel = False
MyBox = MsgBox("Close Workbook?", vbYesNoCancel, "Select an action")
If MyBox = vbNo Then
MsgBox "No"
ElseIf MyBox = vbYes Then
MsgBox "Yes"
ElseIf MyBox = vbCancel Then
MsgBox "Cancel"
End If
End Sub

Excel Monkey;309397 Wrote:
The following below displays a message when a workbook is closed.
However if
the user hits the cancel button when prompted with the options
prior to
close, this code still creates the message. This is becuase this
event
is
triggered before the user is presented with the close options. How
do
you
get around this. Is there another event that I can use to verify if
in
fact
the file has been closed.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Cancel = False Then
Msgbox("Book closed")
End If

End Sub


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Workbook_BeforeClose Event and Cancel Option - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=86505)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86505