Is it possible to undo a macro action?

Question

I want to know if we can undo the macro action by any chance. I am using the excel sheet as a form and I am having a submit button(Macro) which takes the sum of counts of the sheet(based on the form input) and stores it in the next sheet.

My problem is, if we press the submit button without completing it or if we press it twice, the sum which I store in the next sheet, becomes inaccurate. If there a way we can undo the macro actions in excel? I tried using the undo button, but it didn't work for macros. Is there a way we can undo it?

Can we add another macro which would undo the previous macro's work?

Answer 1

I agree with all the commenters who've suggested that the best practice is to validate the starting conditions and/or input values before allowing the macro to make any changes. However, validation is often very complex and totally impractical in a lot of "we need it now" situations.

Here are two very easy things I can suggest:

1) Have the macro save the workbook before any changes are made, but not save the workbook after the changes have been made. This way, if you see something went wrong, you can just close and reopen the workbook and you'll be back to where you were before the macro did whatever the macro does.

2) Have the macro save copies of any affected worksheets before taking any action, so, if things go wrong, you can revert (or create a macro to revert) back to the starting point.

The first option requires the least amount of code, just:

ThisWorkbook.Save

before letting the macro do whatever the macro does.

I frequently use this method when testing macros.

The second option is a little more complex, but not much:

ThisWorkbook.Worksheets("YourWorksheet").Copy(After:=ThisWorkbook.Worksheets("NameOfSheetYouWantItToAppearAfter")

Note that this will activate the copy. If necessary, you can reactivate the original worksheet like this:

ThisWorkbook.Worksheets("OriginalWorksheet").Activate

I hope that helps!