macro clears undo stack - can I stop it doing that?

[Stuartzz]

Hi there
I have VBA in the worksheet in the attached workbook which is designed to copy down formulas (columns e:f and m:al) down to rows which contain text in any of columns g:j.

So if, for example, if I go into cell g6 and input something and press enter, formulas are copied down from row 5 into row 6. If I then go into cell g6 and press delete, then the formulas are cleared from row 6.

But when I make aforementioned changes in the worksheet, I can't undo them by pressing the undo button or ctrl+z.

So, in the attached workbook, if I go into cell g6 and press delete, the text in cell g6 is lost forever.

Is there a way to stop the macro from clearing the undo stack?

Thanks, Stu

Please Login or Register  to view this content.

[MrShorty]

To my knowledge, there is no way to prevent VBA from clearing the undo stack. You can write your own undo procedure that can undo the changes your procedure made (usually requiring your procedure to store everything somewhere before it makes changes) using the application.onundo method https://docs.microsoft.com/en-us/off...ication.OnUndo
This thread links to a tutorial using class modules to create a more generic undo handler https://www.excelforum.com/excel-pro...tion-back.html but I believe it still cannot prevent VBA from clearing the undo stack. It only allows you to undo changes made by the most recently run procedure.

[Leith Ross]

To be clear on this point, unlike the Excel application VBA does not implement an UnDo function. Excel can do this because a temporary copy of the workbook is open until the main workbook is closed. The temporary file is where changes are made to.

In VBA, you can only undo the last change you made in a procedure using Application.Undo. The reason is there is no running list of user changes like in Excel.

[Stuartzz]

Okay thank you both very much for explaining that.