undo option is not working in MS excel

[cssriraman]

Hi all,

I created some macros in my excel sheet. After creating the macros the undo option is unavailable. I don't know why?

Let me know your suggestions.

Thanks,

CS.

[RobDog888]

Macro changes are not "undo-able". Macros can clear all items from the undo list too.

[cssriraman]

That's what I am telling? How to avoid that?

CS.

[RobDog888]

You cant. Manual changes can be undone but not all, like almost any menu item click or action. You would have to write code to log what changes your macro code makes and basically create your own undo log for macros.

[cssriraman]

How can I do that?

Give me some tips/code.

Thanks,

CS.

[RobDog888]

Ok, say your macro procedure is adding a row, you would write out to a hidden sheet or a text file something like "Insert row at row 3" if your code added a row at row 3. This would allow you to go back and delete that row since you know the action and place. You may even want to write it as parameters for a UndoMacro procedure type thing.

[DKenny]

Or you could .....
1/ save a copy of your workbook as a temp file.
2/ run your code.
3/ Show user the outcome and ask if they want to keep the changes.
3a/ If yes then delete the temp file.
3b/ If no then copy temp file back into the original.

[RobDog888]

A good suggestion DKenny as long as the closing and opening interruption is acceptable then that is the solution I would recommend.

[DKenny]

In fact, you may not even need to save it as a temp file, just make a copy of the file in the active instance, use it if necessary and then close without saving. - Depends on the size of the file, etc...