CODEWITHSUNDEEP
excelvba
rodedo
rodedo

Reputation: 822

How to determine user answer to "want to save your changes" prompt in excel vba

As explained in the answer of Determining if Save, Dont' Save or Cancel is Used in VBA, when closing an Excel where the user made changes, first the Workbook_BeforeClose will be called, then the

enter image description here

prompt will be shown and then, if the user selects Save, the Workbook_BeforeSave will be called. But how can I check if the user selected Don't Save, Cancel or clicked on the X to close the prompts (that is the same as cancelling)?

Reason for asking is that I am doing some actions when the workbook is open and I would like to revert them if the user decides not to save the workbook.

Upvotes: 0

Views: 122

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You would just need to create your own prompt to the user for that event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ' Check if workbook has been saved
    If Not ThisWorkbook.Saved Then

        Select Case MsgBox("Would you like to save?", vbYesNoCancel)
        Case vbYes
            'User decided to save, no need to revert changes.
            ThisWorkbook.Save
        Case vbNo
            'Perform extra revert tasks
            ThisWorkbook.Saved = True
        Case vbCancel
            Cancel = True
            'Perform your extra revert tasks, but don't close workbook
        End Select

    End If

End Sub

You can also customize this a bit more with a Userform close dialog if you wish, but the concept would remain the same.

When the user clicks no, you would simply perform the revert tasks you need, then mark the workbook as saved via ThisWorkbook.Saved = True to prevent the prompt from appearing again.

Upvotes: 0

Related Questions