CODEWITHSUNDEEP
vbaexcel
user2739243
user2739243

Reputation: 1

Excel VBA: Undo all normal Excel-changes since last save, then run macro and save

I'm having serious trouble solving this issue:

I have an Excel Workbook (2010 or later) with a Macro that HAS TO run BeforeClose. It deletes internal data and saves this change: (no problem so far)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim xlSheet As Worksheet

For Each xlSheet In Sheets
    If xlSheet.Name = "internalDataSource" Then
        MsgBox "Internal data will be deleted from the workbook. Changes are saved."
        Application.DisplayAlerts = False
        xlSheet.Delete
        ThisWorkbook.Save
        Application.DisplayAlerts = True
    End If
Next xlSheet

End Sub

My Problem: the user should be able to change something. Now if the user changes something he usually gets asked, if he wants to save the changes. In this version there is no choice, but saving his changes. currently they are always saved.

How can I get the user to be able to say "No, don't save my changes." but still run the macro and save the deletion of internal data afterwards?

I'm desperately looking for a VBA-command to revert all changes to the last saved state. Then I would simply run the Macro from above and save my deletion.

Thanks for any help I get!

Upvotes: 0

Views: 3006

Answers (1)

Aaron Thomas
Aaron Thomas

Reputation: 5281

This could be done by saving a copy of the workbook each time a normal save is done, move the internal data to the copy, and rename the copy as the original. There are few options for how to do this specifically, I'll post some suggestions here to flush out my answer:

Saving a copy could be placed in the Workbook_BeforeSave event.

Renaming the copy to the original could be done in the Workbook_BeforeClose procedure you have listed here.

To run a messagebox past the user, you may consider editing the MsgBox you have listed to something like:

If (MsgBox("You are attempting to exit." & vbNewLine _
& "All changes since the last save will be lost." & vbNewLine _
& "Okay to proceed?", 308) = vbNo) Then
  Cancel = True
End If

Of course, not "all changes since the last save will be lost" because your internal data will be saved.

Upvotes: 1

Related Questions