Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose(Cancel As Boolean) - Cancel won't work | Setting up and Configuration of Excel | |||
Workbook_BeforeClose Event and Cancel Option | Excel Programming | |||
Workbook_BeforeClose: Cancel doesn't work | Excel Programming | |||
Workbook_BeforeClose - Cancel not working | Excel Programming | |||
Detecting a cancel on Workbook_BeforeClose event? | Excel Programming |