Creating An Undo Handler To Undo Changes Done By Excel VBA

Pages in this article

  1. Class Modules (1)
  2. Class Modules (2)
  3. Implementation
  4. Conclusion

Conclusion

I have demonstrated a method to add an undo handler to your project. Of course implementing this method means you will have to change existing code that performs actions that may need to be undone later.

The actions the undo handler can undo are limited to changes to properties of objects in general. Things like inserting or deleting sheets, refreshing querytables, updating Pivottables and etcetera, cannot be undone using this technique.

Download

I have prepared a download for you with example code. Find it here.

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me.


 


Comments

Showing last 8 comments of 65 in total (Show All Comments):

 


Comment by: Rich (9-2-2019 18:38:40) deeplink to this comment

Has there been any further info from Warbe ? on (7/17/2018 7:47:36 PM), he said "I am about to publish this implementation here if you don't' mind " but I don't see any more info on this.

Thanks for a GREAT page!


Comment by: Irian (11-3-2019 17:06:24) deeplink to this comment

Good afternoon,

thank you for all the hard work you have put into this. However, I can't get it to work.

In you implementation you write: 'If you go for (1), the OnUndo statement can just describe the set of actions done by this routine. Should you prefer to do (2), remember to change the text of the OnUndo statement accordingly.'. Could you emphasise what you mean by this as it is unclear to me what I have to edit in the OnUndo statement.

Also, I have other VBA code running, for instance to change the color of the selected row. Do I have to edit any of that code to work with the undo functionality?

I hope you can help me with this, as it would be a great functionality!

Kind regards,
Irian


Comment by: Jan Karel Pieterse (12-3-2019 09:59:44) deeplink to this comment

Hi Irian,

It means that if you want to enable the user to Undo each VBA change in the reverse order in which they were done, rather than all VBA actions in one step, you will have to update the Undo text after each undo so it states what the next undo would undo. this is done by using the Application.OnUndo statement as shown in the code, but using a different text as its first argument.


Comment by: JON FEENSTRA (18-7-2019 17:50:00) deeplink to this comment

Hi,

What is the purpose of the following code in Class Modules(1)?

If mUndoObject Is Nothing Then
End If
If mvNewValue = "" Then
End If
If msProperty = "" Then
End If

Thanks!


Comment by: Jan Karel Pieterse (24-7-2019 13:28:00) deeplink to this comment

Hi Jon,

I'm afraid I've forgotten. I presume these were there as a placeholder to do checks if the correct information is present when exectuting the change, but I don't recall whether that is correct :-)


Comment by: Kurnia D Iskandar (10-12-2020 16:14:00) deeplink to this comment

Thank you this is awesome!


Comment by: Loet van Kimmenade (23-6-2021 12:04:00) deeplink to this comment

Kunnen ook afbeeldingen worden toegevoegd en ongedaan worden gemaakt?


Comment by: Jan Karel Pieterse (23-6-2021 13:56:00) deeplink to this comment

Hoi Loet,

Niet zoals de code nu geschreven is, maar ik denk wel dat het mogelijk is om er een aparte functie voor toe te voegen aan de klasses.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].