Try the following code in the thisworkbook module.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ChAction As Variant
If ThisWorkbook.Saved Then Exit Sub
ChAction = MsgBox("Do you want to save the changes you made to '"
& ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation)
Select Case ChAction
Case vbCancel
Cancel = True
Case vbYes
SafeSave True
Case vbNo
ThisWorkbook.Saved = True
End Select
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI Then Exit Sub
SafeSave
Cancel = True
End Sub

Private Sub SafeSave(Optional bClose As Boolean = False)

'!!!your saving code here!!!

With Application
.EnableEvents = False
ThisWorkbook.Save
.EnableEvents = True
.ThisWorkbook.Saved = True
If bClose Then ThisWorkbook.Close False
End With
End Sub

By the way, you cannot secure highly confidential information in a
spreadsheet by this route as its pretty easy to unprotect sheets and
unhide cells. If it really is important that there is no unauthorised
access of the information then if you continue to use Excel you will
need to encrypt the confidential information as well.

Peter Grebenik