Application.EnableEvents causing undo/redo problem

Hi

In the attached workbook when I run the "Disable Macro" the Events (currenty selection change is used) fails but undo redo work fine i.e if I type something and then undo ,it successfully undoes.

Now If I run "Enable" macro then the events do fire but now the undo and redo don't work

How can I get both the functionalities at a time?

Help would be greatly rather GREATLY appreciated

Best Regards
Imran Bhatti

To clarify, you say that with "Disable Macro" undo/redo work fine if you MANUALLY type and then undo/redo correct?

When you have it set to "Enable" are you also MANUALLY entering something and trying to undo/redo or do you mean having it undo/redo something the macro did?

I ask as undo/redo just plain doesnt work for something a macro does. If you have a macro enter content in a cell for example, undo is greyed out. If you manually entered the same thing it would be possible to undo.

If however you manually entered the content in both cases it would take some digging to see why it has been impacted.

Thanks ZeroCool for the reveiw

Yes I am doing changes manually. I mean if I type something in the sheet with typing,change the font size of any cell manually, or I accidentally delete something manually and I immediatedly reliazed it was not to delete then I should be able to undo/redo that manual change with the default buttons of undo/redo.

Hope It makes sense.

Best Regards
Imran Bhatti

Yes that clarifies. So in both cases we are talking about manual changes being undone or redone. That is odd that undo/redo would be affected then.

Ill check out the file see what we can find

O yea that makes sense now.

So lets talk about what is happening here in relation to what we discussed about macros not allowing undo/redo.

When you disable events and then type something into a cell, as soon as you are done typing what do you do? hit enter or tab right? That effectively changes the active cell right? Well you disabled events so your macro doesnt fire off in this case and the last thing you did was a manual entry.

When you have events enabled and then type something into a cell, as soon as you hit enter or tab you are executing your macro because events are now enabled, meaning the very last thing you did after making that entry was run a macro, hence Excel cannot undo/redo as the last thing you actually did was run a macro, not make a manual entry

How can I get both the functionalities at a time?

I usually try to be optimistic, but I don't think you'll be able to retain both event code and the undo stack. Everytime a VBA macro is called (and a selectionchange macro is going to get called frequently -- everytime you move the cursor around the spreadsheet), the undo stack is cleared, and there is nothing (that I am aware of) to change that behavior of Excel. A choice to use event procedures (especially frequently called events like selectionchange) is a choice to not have a reliable undo stack, and the choice to have a reliable undo stack is a choice not to use event procedures. You as the programmer need to choose which is more important -- the event procedure or the undo stack.

Thanks ZeroCool and Mshortly for reviewing the problem

I found something that is near to what I am after.
http://www.jkp-ads.com/Articles/UndoWithVBA00.asp
How can we adopt it for my WB? I have attached the readymade sample available at the above link.

Best Regards
Imran Bhatti