12

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?

haimen
  • 1,985
  • 7
  • 30
  • 53
  • 5
    not with native functions, The only way to do it is through vba. There are many examples on how to do it, they are fairly intensive. – Scott Craner Nov 19 '15 at 20:32
  • 2
    Do a little research and you will find your answer. There are answers on Superuser and elsewhere. – Ron Rosenfeld Nov 19 '15 at 20:34
  • 4
    I would recommend instead of looking for an undo when these mistakes are made, check for the mistake before submitting. It's pretty easy in your code to put together an if statement where it throws an error if there are incomplete fields or if the sum value already exists in the second sheet. No need to worry about undoing mistakes if the mistakes can't happen. – Dan Donoghue Nov 19 '15 at 21:00
  • Kinda redundant but come on, running non benign macros with no backups is a gamble. – helena4 Nov 19 '15 at 21:24
  • absolutely agree with Dan. – PaulG Nov 19 '15 at 21:25
  • 1
    As has been mentioned, VBA Undo is pretty complicated. I agree with @DanDonoghue, add some kind of check in your code instead. This is much easier, and will cause way less headaches working out over doing an Undo function, which I'm sure would turn into a rat's nest pretty quickly. ", if we press the submit button without completing it or if we press it twice" - just add some code that makes sure the fields/cells/whatever are complete. Also, a messagebox saying perhaps "This will take some time, please don't press 'Submit' again"? – BruceWayne Nov 19 '15 at 21:31
  • Although in saying this I did build an undo function into one of my macros, it wrote product ID's to a database against a report ID (it was a reporting system and saved your report criteria on the DB so you could run anytime from any machine) and as it was saving the details you could cancel it and back out the changes, it basically wrote a log of what it was doing then polled through the log deleting changes it had logged as being done. – Dan Donoghue Nov 20 '15 at 00:28

1 Answers1

5

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!

SeanW333
  • 479
  • 4
  • 9
  • 1
    Thanks a lot for the tip. I used `ActiveWorkbook.Save` instead since my macro was in a different workbook from the one it was destroying. – Noumenon Jun 03 '19 at 20:51