CODEWITHSUNDEEP
c#excelvstooffice-interop
Sanja Melnichuk
Sanja Melnichuk

Reputation: 3505

Cannot trap after "Want to save your changes" dialog in Excel

  1. In Word VSTO we have DocumentBeforeSave event which is called right after "Want to save your changes" dialog and I can easy cancel standard Save dialog and show my own.

  2. But in Excel VSTO WorkbookBeforeSave is called after Save dialog closed and it causes my own save dialog to be shown after built-in one. I can use WorkbookBeforeClose event but I should show my own "Want to save your changes" dialog and also autosave functionality will not work when pressing "Don't save".

Is there way to call my code right after "Want to save your changes" dialog in Excel with ability to prevent built-in "Save" dialog or somehow tell Excel to create an autosave point (with my own "Want to save your changes" dialog) when I press "Don't save"?

Upvotes: 6

Views: 719

Answers (1)

michaelf
michaelf

Reputation: 499

I'm not sure if you found your answer as this thread is a bit dated but thought I'd put in my $0.02.

I create a [isDirty] named range on an unlocked veryhidden sheet and on each visible Worksheet.Change I set the [isDirty]=True

In the Workbook.BeforeClose I have this:

      If [IsDirty] = True Then
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation)
                Case Is = vbYes
                    ThisWorkbook.Save
                    [IsDirty] = False
                Case Is = vbNo
                     'Do not save
                Case Is = vbCancel
                    Cancel = True
            End Select
     End If

In Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ThisWorkbook.Save
    [IsDirty] = False
    Cancel=True

Upvotes: 0

Related Questions