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

6
votes

  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"?

1
c#excelvstooffice-interop
@vba4all Yes I am sure. Try click close with new document. With saving from ctrl + s and File-> Save all ok – Sanja Melnichuk
Are you setting SaveAsUI to False and Cancel to True? – user4039065

1 Answers

0
votes

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