Thank you Paul and sorry for responding late.
I went ahead with the 1st solution you gave with a msgbox to save the sheet before closing. It works fine.

There is one problem left - the user form has the option of being cancelled by clicking the cross on the top right corner. For some reason, if I click that, the workbook gets saved. I would like to return to workbook without it being saved. Any suggestions please?

Thanks a lot!


Quote Originally Posted by Paul_Hossler View Post
Try this bit of logic

'Save' doesn't close the WB, but just saved

'Close' shows the Save dialog allowing you to save, but if you Cancel that, it just exits




Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)


    bCloseCalled = True


    Load UserForm1
    UserForm1.Caption = "Workbook_BeforeClose"
    UserForm1.cbLogExit.Caption = "Log Comments, Save, and Close"
    UserForm1.Show




    Cancel = bCancel
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not bCloseCalled Then
        Load UserForm1
        UserForm1.Caption = "Workbook_BeforeSave"
        UserForm1.cbLogExit.Caption = "Log Comments and Save"
        UserForm1.Show
    End If


    Cancel = bCancel
End Sub