VBA to Undo routine after erroneous entry?

[esskaykay]

Is there a way to trigger an Undo command (Ctrl+Z) via VBA – something like: Application.SendKeys ("^z").

I have a routine that checks if a condition exists after an entry is made (which updates other cells). If the condition exists a message appears stating you cannot do that. After clicking OK, I want the routine to trigger an Undo (Ctrl + Z) returning all cells to the situation prior to the erroneous entry.

I can manually click the undo arrow and all works fine. Can I do this via VBA by adding this to the end of the routine after the erroneous message appears which would reset all back to the condition prior to the erroneous entry?

Thanks,
Steve

[Artik]

OnUndo method.

Artik

[esskaykay]

Thank you Artik,

I am confused as to how exactly the Application.OnUndo is suppose to work.

I’m still not able to “undo” the action. I am calling a macro (PaymentLimit) in my Worksheet_SelectionChange routine. If the maximum number of payments has been exceeded the macro is triggered which affects other cells. I click on the OK button and press CTRL+z and all reverts back to the status before the erroneous entry which is great.

Here’s the PaymentLimit –

Please Login or Register  to view this content.

I am confused as to where to place the UnDo command and how to be coded. As a test, I created a second macro called UndoLimit. I tried running it via a shortcut key after clicking on the message box OK button but nothing happed.

Here is the UndoLimit –

Please Login or Register  to view this content.

What I was hoping for was simple way to place a Ctrl+Z command after the message box appears but apparently that is not possible. Any other suggestions or assistance would be appreciated.

Thanks again,
Steve

[Artik]

That's not how it works. You have to write yourself a procedure that will reverse the effects of the macro that " made a mess" for you. You indicate the name of this reverse procedure in the OnUndo method. Preparing such a procedure is often difficult and sometimes impossible.
If it is critical for you such a moment, you should check all the conditions beforehand "in memory", without writing to the cells. Only when the data has successfully passed all the tests can you enter it into the cells. The "Undo" stack is cleared when the code action interferes with the structure of the workbook. Any change in the code of a value in a cell, its formatting and others immediately clears the Undo stack.
Perhaps you should think about creating a form where you enter all the parameters for critical calculations. There you will check all the conditions and, if they are met, only then will you allow the data to be written to the worksheet.

Artik

[esskaykay]

I understand. I have no idea how to reverse the entry once processed so I guess I will just pass on this . I really don't want to create an entry form because it will actually be quite rare that this is triggered. It sure would have worked easier MS would have included a Sendkeys ("^z") or some similar option. Oh well, now I know.