Reverse Macro after it has run

[markh1182]

Hi, I have some VBA that is run via a macro. It can put the words To Summary in a cell or puts a sum formula in others.
Is there a way run a macro that would undo these changes?

Thanks, Mark

 

[erik.van.geit]

Hi, Mark,

the easiest UNDO is when you ask WITHIN the macro to confirm
like this

Option Explicit

Sub undo_macro()
'Erik Van Geit
'050411 0109

Dim act_sh As Object, temp_sh As Object
Dim act_sh_n

    With Application
    .ScreenUpdating = False
    .EnableCancelKey = False
    End With

Set act_sh = ActiveSheet
act_sh_n = ActiveSheet.Name

act_sh.Copy After:=Sheets(Sheets.Count)
Set temp_sh = ActiveSheet
temp_sh.Visible = False

'code
'this line is just an example
act_sh.Activate
Range("A1") = "new entry"

    With Application
    .ScreenUpdating = True
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With

    If MsgBox("Do you accept the changes", 36, "ACCEPT") = vbYes Then
    temp_sh.Delete
    act_sh.Activate
    Else
    act_sh.Delete
        With temp_sh
        .Visible = True
        .Name = act_sh_n
        .Activate
        End With
    End If
    
    With Application
    .ScreenUpdating = True
    .EnableCancelKey = True
    End With

End Sub

depending on what exactly you would like to undo, another strategy will be necessary
more details would help

kind regards,
Erik

 

[markh1182]

The macro runs so that the spreadsheet has the necessary formulas in to print out. However if they realise after that they need to add in extra lines, the formulas will no longer be at the bottom of the page where they were specified to be. So what I want is to take out the formulas that were added so it can start again.

 

[erik.van.geit]

Hi,
did you see my reply ?
4 minutes to read + test + write + post a reply looks short to me

 

[Joe Was]

The Un-Do Function is independent of changes made by a macro or code. So to write good code you need to also write good error traping and exit code part of that Error and Exit code is a way to undo the changes as best you can. Most of the time it takes more code to un-do someting than it did to do it in the first place!

 

[erik.van.geit]

Hi, Joe,

hence my advice to work on a copy of the sheet and confirm or cancel
if possible this is the easiest route to my sense

 

[Joe Was]

To back-out the formulas you need to define a Public Variable to hold the range the formulas are in and then if needed another Sub or code can use that range to delete that Row/Column or over write the formulas or remove the formulas in that range.