Bypassing "Undo"

[GordonPSmith]

I've got a macro that updates great quantities of cells iteravely. From the point of view of Excel, when I change one cell, I then go and change all those others. From time to time, I lose the "Undo" function entirely, which I believe is just Excel's way of throwing up its hands because it can't keep track of alllllllll the changes that automatically happen when I adjust one cell.

So, in a flash of insight, I realized that I don't actually _need_ excel to keep track of all the individual changes. The code does that. So, my question becomes, "is there a way to turn off what feeds to the undo buffer?" Ideally, this would look like:

1) I change a value, which is recorded in the undo buffer
2) The macro turns off the buffer, and proceeds to adjust, say 200 entries on three tabs. At the end, the macro turns the buffer back on
3) Now, the buffer only holds the original value of the single cell I changed in step 1, and I can move on, cell by cell.

If I need to undo, I can, but the undo would only revert the values I altered on the control tab. The macro-altered cells will automatically recalculate and go back to what they were, and peace will reign throughout the land.

Thanks!

[shg]

Excel isn't throwing up it's hands; as I said in your last thread, when a macro changes a cell (or most anything in the environment) the undo stack is flushed.

[rscsmith]

A possible solution would be to copy the old cell value to the clipboard. That way, if you want to undo, you can simply paste the value back in. Not ideal, I know.

Alternatively, keep track of your changes in a log file/hidden cell/global variable. Track the address and previous value. You will need to get the previous value on selecting the cell, prior to edit, as the change event only captures the address, not the previous value. Then, have an undo macro button that simply copies it back in.