The following event procedure will prompt for a password. If the
password is correct, it will save the file and then close. Else it will
cancel the closing process.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
myStr = InputBox("Password required for closing")
If myStr = "my set password" Then
Me.Save
Else
Cancel = True
End If
End Sub

To install:
Alt+F11 to go to the VBA IDE
In the Project Explorer (top-left subwindow) double-click the
ThisWorkbook icon.
In the code page that appears paste the code above.

HTH
Kostis Vezerides