Getting the most out of the OnUndo Method

Some months ago, I stumbled across this article on John Walkenbach's site.

I filed it away, in the event it ever became useful. Last month, I finally used the methods John describes to add Undo and Redo capability to a Dictator application I have been developing for the last couple of years. In general, I am very pleased with the results.

One thing that I picked up from the Microsoft Help is that running a macro clears the Undo stack, and then the OnUndo method re-populates the last position, and only the last position of the stack. Obviously, it's a LIFO stack, even though the OnUndo method only gives access to the last position.

I was wondering if anyone else had done much playing with this. If the developers had provided a way to populate the Undo Stack to greater depth, I would expect to have found some mention in the online help, or at Microsoft.MSDN.Net. But there is nothing. So I am thinking about a Static Array, or a table on a hidden sheet to store my own Undo Stack... I don;t know... probably more trouble than it's worth... but sometimes, it's nice to be able to Undo several commands, rather than only One.

Any feedback, comments or discussion are appreciated...

I don;t know... probably more trouble than it's worth...

Click to expand...

Rather than writing tons of code, or possibly bloating your workbook with "saved" data in a hidden worksheet, or using up memory to store static arrays, I would think it may be more useful to train the users properly. Tell them, "Save Save, Save! And don't save as the same version! Rename it, darn it, so that you can go back to it if needed! And while you're at it, clean up your desk! This place is a mess! How can you think? Oh, that's right, you can't because if you could you'd remember to rename and save your workbook...but no...you had to go and save it as the same version and now you're S.O.L., my friend...S.O.L!"

Sorry, got side tracked there fro a moment...

Anyway, sorry, can't say I know a way to do what you're asking. Sure would be nice though. I have to wonder what causes the Undo stack to empty anyway? And I also have to wonder why MS doesn't do something about it...seems a little buggy to me.

If my english is good enough to understand

running a macro clears the Undo stack

Click to expand...

To my sense it should be:
Only macros which change something to the workbook will clear the Undo stack & only under certain "logic" circumstances.
("logic" means what Microsoft finds logic) I think - not sure - that as long as the Activesheet is NOT changed (writing, changing shape, changing filtermode, ...), you can still use undo.

Type "hallo" in Sheet 2 A2
Run
You can still undo the "hallo"

Repeat test typing hallo in Sheet 1 A2
No undo anymore: reason (I think) change was made on same sheet.

Nice example:
Even when you run the rather long "Table-It" procedure, which is creating another workbook, change filtermode, deleting values, ...., deleting new workbook, you can still use UNDO.

kind regards,
Erik

Using XP

TOO FUNNY!

Seriously... you have a point about over-compensating for clueless users... then again, anyone who isn't a developer like us is typically a clueless user, so we are completely outnumbered.

EDIT: Erik, you slipped in with yor reply while I was replying to ExcelChampion. Interesting observation that only certain events trigger the Stack Clear. The Microsoft Documentation does not make that distinction, which I find inetresting. I'm not sure how useful this behaviour is in my case, since all of my macros involve manipulating Shapes on the ActiveSheet. I'm going to need to think about it a little deeper

Over compensating for idiots is a huge task. You have to think of every possible stupid thing that someone could do to your program. As an example, I wrote an application for myself to make one of my jobs more efficient. It took about half a day to write and I never had any problems with it because I knew how it worked. Recently this particular job was handed on to someone else so I provided them with my application and showed them how to use it. 10 minutes later, I get a call “Urm, its got an error and I don’t know what to do”. After spending an hour trying to diagnose the problem I decided to build in some error handling, etc. to ensure that this new user couldn’t possibly break it again. This additional work that had to be done took me the best part of a week to complete!! So far so good and I haven’t heard of any problems since but it seems crazy that it should take so much more time to “safeguard” your stuff from idiot usage.

Over compensating for idiots is a huge task. You have to think of every possible stupid thing that someone could do to your program

Click to expand...

But this is essential for a good program.
Just think how difficult it must be for Microsoft.

see this post for a clever use of the undo command by Boller:

http://www.mrexcel.com/board2/viewtopic.php?t=245736