FAke UNDO button

[Alex Piotto]

Hi everybody!
I have a book with some macros that will move data between sheets. As it is almost impossible to undo a macro job, i need a fail safe.
After scavenging online, I made a button with a script that will close the book without saving, and in theory should open it again.
Here goes the simple script...

Private Sub UNDOBUTTON_Click()

x = ActiveWorkbook.Name

Workbooks(x).Close savechanges:=False

Workbooks.Open Filename:=ActiveWorkbook.Path & x

End Sub

Sure is that the book will close without saving but it is clear that it will not open again (and it do not open actually) since the macro stops when the book closes.

Any workaround?

 

[Trebor76]

Any workaround?

No, none I can think of as once the workbook closes the code terminates.

Maybe firstly copy the data to a hidden tab from which the data can be retrieved from if need be?

 

[Alex Piotto]

Hi Trebor76, thank for answer. I think I will made a button that just closes the book without saving. Then I will reopen it manually...
I may have to call it REVERT TO SAVED and not UNDO...

 

[Jaafar Tribak]

Hi Trebor76, thank for answer. I think I will made a button that just closes the book without saving. Then I will reopen it manually...
I may have to call it REVERT TO SAVED and not UNDO...

How about making the workbook Read-Only ?

 

[mikerickson]

Perhaps if that code were in the Personal Macro Workbook, it could close and reopen the other (active) workbook.

 

[dmt32]

Hi
as an idea how about saving the active file under another name this should (in theory) allow you to open your file without the changes. You can then close & delete the renamed file.

Not fully tested but something like this

Sub Undo()
    Dim ActiveFile As String
    Dim wb As Workbook
    'create temp file name
    Const TempFile As String = "Temp.xlsm"
   
    On Error GoTo myerror
    'set object variable to active workbook
    Set wb = ActiveWorkbook
   
    With Application
        .ScreenUpdating = False: .EnableEvents = False: .DisplayAlerts = False
    End With
   
    With wb
    'store active file name & path
        ActiveFile = .FullName
    'save file under temp name
        .SaveAs Filename:=wb.Path & "\" & TempFile, FileFormat:=52, _
                          Password:=OpenPassword, WriteResPassword:=WriteResPassword
    End With

    'open file
    Workbooks.Open ActiveFile
    'close temp file
    wb.Close False
    'delete it
    Kill wb.Path & "\" & TempFile
   
myerror:
    With Application
        .ScreenUpdating = True: .EnableEvents = True: .DisplayAlerts = True
    End With
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Idea may need some adjustment but hopefully, give something to work with

Dave