Microsoft Excel: Force your workbook to save before closing (VBA)

If you habitually forget to save your changes before closing a workbook, here’s a quick solution. Use VBA code to make sure that a workbook can’t be closed unless it’s saved first.

1. Open the workbook where you want to incorporate this feature.
2. Open the Visual Basic Editor (From the Tools menu, go to Macros and select Visual Basic Editor or press ALT+F11).
3. If the Project window is not shown, go to the Insert menu and select Module.
4. Enter the following code:

   Private Sub Workbook_BeforeClose(Cancel As Boolean)
   'If workbook is not saved, cancel and display message.
   If Not ThisWorkbook.Saved Then
        Cancel = True
        MsgBox "You MUST save the workbook before closing!"
   End If 
   End Sub

When you close the VB Editor, you will no longer be able to close the workbook without saving your changes first.

If you have any questions regarding this tip or need help implementing it in your own projects, contact Ranelle Maltas, Application Support Associate, at 472-0585 or e-mail at rmaltas2@unl.edu.