Workbook_BeforeClose Event and Cancel Option
 

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' (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

Workbook_BeforeClose Event and Cancel Option
 

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' (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