msg box at close

K

kcdonaldson

I would like a message box to apear when the user closes the workbook that
would remind them to check certain things in the department before they save.
Can anyone help me with the code for this?
 
N

Norman Jones

Hi KC,

Try:

'================>>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Your message"
End Sub
'<<================

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module):

******************************************
Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
******************************************
 
N

Norman Jones

Hi KC,

Replace my code with the following:

'=============>>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim res As Long

res = MsgBox(prompt:="Have you forgotten to...", _
Buttons:=vbYesNo)

Cancel = res = vbYes
End Sub
'<<=============

Now, if the user responds yes to the message box, the file will remain open;
otherwise, the file closes.
 
Top