Creating An Undo Handler To Undo Changes Done By Excel VBA

Comments

All comments about this page:

Comment by: DM Unseen (9-3-2006 06:25:32) deeplink to this comment

Nice One JK!

Some comments

mUndoClass.AddAndProcessObject ActiveSheet.Cells(i,1),"Interior.Colorindex", 15

Could also be

mUndoClass.AddAndProcessObject ActiveSheet.Cells(i,1).Interior,"Colorindex", 15

because most properties return objects.

In general if you have property assignment:

object1.object2.object3.property1 = <XYZ>

you could parse this as

mUndoClass.AddAndProcessObject object1.object2.object3,"property1", <XYZ>

although controlling where you split your object and property is good, maybe a function that parses it would be handier:

mUndoClass.AddAndProcessAssignment "ActiveSheet.Cells(i,1).Interior.Colorindex = 15"

or

mUndoClass.AddAndProcessAssignment "ActiveSheet.Cells(i,1).Interior.Colorindex" ,15

Could work. You would need to extract the property name(and maybe value) by searching for the dot. This can give issues in some circumstances (i.e. default properties), but it would be easier to code with.

Comment by: DM Unseen (9-3-2006 06:30:22) deeplink to this comment

I also see that you can only process property assignments that are not objects!

Comment by: Jan Karel Pieterse (9-3-2006 08:37:59) deeplink to this comment

Hi DM, Good suggestions!

Comment by: Vyacheslav Maliuhin (2-3-2007 05:55:22) deeplink to this comment

Thank you very much for your tutorials! But i think, this UNDO implementation best suits for large projects. Here is a tutorial for small projects:
http://j-walk.com/ss/excel/tips/tip23.htm

Comment by: Jan Karel Pieterse (2-3-2007 07:31:57) deeplink to this comment

Hi Vyacheslav,

I agree with that. And thanks for the link.

Comment by: Jac (6-12-2007 02:09:52) deeplink to this comment

Hi,

I can't seem to make the UNDO work for Conditional Formatting.

It can restore the previous formatting but the formula for the Conditional Formatting still remains.

Anyone knows how to use VBA to add conditional formatting and use this class to undo?

Comment by: Jan Karel Pieterse (6-12-2007 03:12:30) deeplink to this comment

Hi Jac,

Conditional formatting expects you to enter the formula in Excel's locale NOT in US syntax.

So with my regional settings (list separator = ;)

This line works:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1;a2)=1"

But this one fails:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1,a2)=1"

So: The Formula1 (and 2) property of a formatcondition object reads AND writes LOCAL formula syntax in VBA.

This is opposed to the Formula1 and Formula2 properties of the VALIDATION object: those SHOW FormulaLocal, but if you want to set them, you must use the US formula syntax.

Comment by: So Theara (10-4-2008 03:04:38) deeplink to this comment

Hi! I saw ur code about undoExcelVba. But I wanna be to see the implementation about Undo&Redo use in Access VBA. Can you shown it?.
Thanks...

Comment by: Jan Karel Pieterse (10-4-2008 05:23:16) deeplink to this comment

Hi So,

I'm sorry, that is beyond my expertise.

Comment by: andrewit (27-6-2008 01:46:39) deeplink to this comment

I suggest you should add command to save the file as a temporary file and then run
the Application.Onundo

It just made it easy....

Comment by: Valdek (5-4-2009 12:13:36) deeplink to this comment

Thanks Jan for excellent code piece.

Working on assume quite useful keyboard shortcuts like align center, align left, align top, paste formats etc. for Excel 2003.

Tried to use it to undo pastespecial command implemented from VBA but seems to me works only on commands with properties having definite value, i.e. align commands - works OK, pastespecial - NO, Merge/Unmerge - NO .

Pastespecial Values could handle by modifying code suggested by Vyacheslav (this code did not take into account possible differences in copy from and copy to range sizes).

Pastespecial formats - this is beyond my timelimit can spend to my hobbyprogramming.

It is a pity of course that Microsoft has not thought of easy shortcut creation routines with full funcionality for most used commands.

Any suggestions on making your code to work with pastespecial ?

Comment by: Jan Karel Pieterse (6-4-2009 00:51:19) deeplink to this comment

Hi Valdek,

Unfortunately not, since you never know what exactly is being pasted and how many cells are affected.
It is doable, but quite a lot of work.

NB: If you use a different approach to action the stuff you are trying to put in kb shortcuts you can keep the undo stack.

For example, there are existing toolbar buttons to do paste formats and paste values. There is also one for the paste special dialog. Put those controls on a toolbar and you can use Application.commandbars("Foo").Controls("Bar").Execute in your code.

Comment by: Manoj D Kapashi (10-11-2010 06:14:23) deeplink to this comment

I am trying to stop a workbook from closing or saving if certain conditions are not fulfilled, and force it to remain open.

Is there some way to do this? I tried to use undo for workbook_before_close and workbook_before_save, but obviously it is not that simple.

Can you pl help?

Comment by: Jan Karel Pieterse (10-11-2010 06:43:37) deeplink to this comment

Hi Manoj,

I kindly request you to go here to ask questions like that:

http://www.eileenslounge.com

There are a lot of people there to help you.

Comment by: James007 (15-1-2011 04:19:16) deeplink to this comment

Hi Jan Karel,

Would it be possible to events such worksheet_change() trapped in a similar way to benefit from your Undo procedure ?

Thanks in advance for your kind assistance
Cheers
James

Comment by: Jan Karel Pieterse (15-1-2011 11:14:41) deeplink to this comment

In principle, yes. This method can be used from any procedure.

Comment by: Sir Shagsalot (26-1-2011 20:46:14) deeplink to this comment

Hi,

First, this undo stuff is a lifesaver for someone like me, who has other things he prefers to do to writing code... :-)

Shame it requires us to actually protocol along every action we do (it also has advantages, but still).

Ideally one would re-structure it so that one can just turn undo for VBA on for a given worksheet or workbook and the code would do the rest.

Maybe use dynamic code insertion into the onchange event of the workbook/worksheet to protocol the change, so we can always restore the worksheet to the "before" state?

Not sure. If I ever find enough time to do some coding instead of my other interests I may try it. Will report back if I do...

Greez SSAL

"Die gefährlichste Weltanschauung ist die Weltanschauung derjenigen, die die Welt nicht angeschaut haben."
(Alexander von Humboldt)

Comment by: Chris Slowik (19-10-2011 12:38:13) deeplink to this comment

Great bit of code here.. I'm busy implementing it in a suite of UDFs that i've distributed to my coworkers.

One thing though, I noticed that this clears the undo stack of anything you did before the action. Is there any way around this? Is there a way to modify this code so it just adds on to the existing undo stack?

Comment by: Jan Karel Pieterse (19-10-2011 23:20:42) deeplink to this comment

Hi Chris,

No, unfortunately you cannot keep the current Undo stack. As soon as you do anything significant in VBA, the Undo stack is erased.

Comment by: Alexander (9-5-2012 13:13:46) deeplink to this comment

Hi Jan Karel
I'm not sure if this is what I need to accomplish my needs. I am trying to implement a “Undo” functionality for macro changes. Currently Excel does not allow this. Your help in this is most appreciated.
Alexander

Comment by: Jan Karel Pieterse (9-5-2012 23:17:14) deeplink to this comment

Hi Alexander,

Well, this is one way of implementing what you need. All I can say is that undoing VBA changes will be something you have to program yourself.

Comment by: Keith Lee (7-8-2012 22:49:53) deeplink to this comment

Hi Alexander,

I am a very NOVICE hoppy VBA programmer.

I have several small VBA procedures that would be nice if they could be undone if the user clicked the button by mistake or changed thier minds after the fact.

Could you show me how to apply your code to the following procedure, after that I should be able to adapt that to my other VBA routines. Thanks in advance.

Sub DeleteActiveRow()


On Error GoTo TyingToDeleteAProtectedCell

    Dim ErrorResult As String
    Dim MyMessageBox As String
    
ActiveCell.Rows("1:1").EntireRow.Select

If Cells(Application.ActiveCell.Row, "Z").Value = "Don't Delete This Row" Then
         MyMessageBox = MsgBox("Wait!!!! You Can't Delete This Row...If you do, some of the Automated Functionality of this Workbook will be lost.", vbCritical, "123PAS Message: Functionality May be Lost!")
         Exit Sub
     End If



    Selection.Delete Shift:=xlUp
    
Exit Sub

TyingToDeleteAProtectedCell:
    ErrorResult = MsgBox("Sorry, there are 'Protected Formulas' on this Worksheet." & vbCr & "Rows CANNOT be Deleted on this page." & vbCr & vbCr & "Please go to either the 'Section L', 'Section M', or 'Section C' Tabs and make your Deletions there. This page will be updated automatically.", vbCritical, "123PAS Error: Trying to Delete Protected Cells")
    
End Sub

Comment by: Jan Karel Pieterse (8-8-2012 13:15:28) deeplink to this comment

Hi Keith,

I'm afraid this code does not lend itself for the undo mechanism I proposed.

What I would do is have the delete code FIRST copy the row that is about to be deleted to some hidden undo worksheet. Then do the deleting. After that, Set Application.OnUndo to some code that can copy back the deleted row(s).

Comment by: Florian (26-2-2013 15:11:08) deeplink to this comment

Hy,

How do you get the Undo Button reactivated after you used it once? When I use the button to execute my undo function I can do what ever i want but the Undo button stays gray.

Greetz

Comment by: Jan Karel Pieterse (26-2-2013 15:35:04) deeplink to this comment

Hi Florian,

If you DL the example workbook, you'll see I designated a couple of shortcut keys to handle the undoes.

Key is, that from VBA you can only set one undo action to the User interface.

So your code would typically hold a collection of actions to undo. The last item of that collection would be handled by the undo command from the UI and the undo code you wrote would add the one-but-last item of the collection after processing that undo command.

Clear as mud?

Comment by: Florian (26-2-2013 15:58:26) deeplink to this comment

Hi Jan,

Thanks for the fast answer and yes it should work like you said, but after i use

Comment by: Jan Karel Pieterse (26-2-2013 16:37:13) deeplink to this comment

Hi Florian,

The workaround is rather simple.

Make sure you put the statement to schedule the next undo in a separate subroutine. From the scheduled undo sub, call the "ScheduleNext" routine using Application.Ontime.

Example:

Comment by: Florian (27-2-2013 08:30:57) deeplink to this comment

Hi Jan,

Many many many thanks! I would never come up with

Comment by: Jan Karel Pieterse (27-2-2013 10:29:37) deeplink to this comment

Hi Florian,

An Excel dev needs tricks up his sleave to get things done :-)

Comment by: Milind (17-6-2013 11:16:29) deeplink to this comment

How do I' do this?
Please provide sample Excel file contains the above mentioned codes

Comment by: Jan Karel Pieterse (17-6-2013 11:28:01) deeplink to this comment

Hi Milind,

There is a sample file mentioned on this very page...

Comment by: Ivan Lanin (5-10-2013 19:08:58) deeplink to this comment

I just want to say thank you. I've managed to use your code to enable undo function for my VBA scripts.

Comment by: Mathan Kumar (13-8-2015 14:29:45) deeplink to this comment

Simply Awesome!

Comment by: Marvin (29-1-2016 20:13:50) deeplink to this comment

Hello!
I just speak english a little.
I wish to know if you can help me?
I have this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 And Target.Row > 2) Or (Target.Column = 4 And Target.Row > 2) Or (Target.Column = 5 And Target.Row > 2) Then
     Sheets("MADRE").Cells(Target.Row, 7).Value = Now
     End If
End Sub

And I want to add your routine for enable the "UNDO".

I send you a great greet.
thnks.

Comment by: Jan Karel Pieterse (29-1-2016 20:58:32) deeplink to this comment

Hi Marvin,

You do understand that the undo method I demonstrate here can ONLY undo the change your code does on this line:

Comment by: Marvin (29-1-2016 23:59:14) deeplink to this comment

Hi! Jan Karel!
Thank you for respond me.
I understand that the code only going to modifify
Sheets("MADRE").Cells(Target.Row, 7).Value = Now

But I guess, I do not expline me well.
I will try it.

I have 3 columns with 3 different prices, I have one 4th more (here I want to put the date when any of the above 3 were modified). But I will discover that if I have a big mistake I cannot do UNDO(CTRL+Z).

I want to know if it is possible add the UNDO at my funciton?
I hope I have explained. I am so sorry if i wrote bad some words or I did not explain me well.
Again, thank you very much.

Comment by: Jan Karel Pieterse (30-1-2016 13:47:13) deeplink to this comment

Hi Marvin,

In a normal module, paste this code:

Comment by: Marvin (31-1-2016 18:38:09) deeplink to this comment

Hai Jan Karel. I did things as you indicated me.
It was great! Thank you very much.
But I have a question more.

I noticed that I just can do one "undo" and not more.
Is there some way with that code that you give me to do n-quantity of "undo".

I hope not cause many problems. I am beginner in VBA.
Again Thank you very much.

By the way, It is the best webside about this topics that I've seen.

Comment by: Jan Karel Pieterse (1-2-2016 09:54:48) deeplink to this comment

Hi Marvin,

VBA can only schedule one Undo I'm afraid, but the routine you tell it to run can of course schedule the next undo for you. If you look at the example workbook that you can download, you'll see how I implemented to undo things step by step or all in one go.

Comment by: Samad Vaziri (5-2-2016 19:04:16) deeplink to this comment

Hi Jan,

I just enjoyed reading your awesome solution for undoing VBA changes. I am going to implement it into my current VBA application. Also I am thinking to add some bookmarks along with a comment in the undo stack to enable undoing a whole bunch of changes done in every interaction with the user. Perhaps I will need to use some hotkeys other than Ctrl+Z to manage undoing the whole changes done since I put the topmost bookmark in the stack.
I will appreciate your valuable comments about the idea.

Regards,
Samad Vaziri        

Comment by: Jan Karel Pieterse (8-2-2016 13:53:30) deeplink to this comment

Hi Samad,

In the example I wrote up here I chose to have control+z undo all steps, but it makes just as much sense to have control+z undo only the last action your undo handler has recorded.

Comment by: William (7-3-2016 07:48:40) deeplink to this comment

Hi Jan,

First of all, thanks for the code it was an eye opener.

I have a question, say I have Rows(5).EntireRow.Delete in my code, would it be possible to undo the above change with your code? Thanks

Comment by: Jan Karel Pieterse (7-3-2016 09:19:23) deeplink to this comment

Hi William,

Yes in theory it would, but it would be quite a bit of work, as you would have to store the original content of the row prior to the deletion.

Comment by: Brian Murphy (7-12-2016 19:15:39) deeplink to this comment

Jan - Your site is like a goldmine!

I'd like to undo a cut/paste.

I use VBA to .Cut a cell range from one workbook to another, make a copy of the .Formula property for later use (b/c this nicely makes all cell references external), and .Cut the cell range back to where it came from (essentially undo the first .cut). This works, but has undesirable side effects on cell references in the source wbk that could be avoided if I could Undo the first cut instead of using a second cut.

Your VBA way of doing Undo doesn't seem to work with a Cut operation. Do you know of way to do this?

Comment by: Jan Karel Pieterse (8-12-2016 07:00:48) deeplink to this comment

Hi Brian,

So that makes you a gold digger, right? :-)
Seriously, Undoing a VBA-invoked cut is very difficult I think.

Comment by: Giang Pham (19-12-2016 09:47:14) deeplink to this comment

Hi Jan,
Your work is amazing.
Worked beautifully as I expected.
Howerver, when I tried to implement your method on Add-in it does not work. For example, I am building an Excel Add-in and I want the user to be able to Undo the functions of that Add-in.

Looking forward to your respond!

Thank you Jan!

Comment by: Jan Karel Pieterse (19-12-2016 10:17:50) deeplink to this comment

Hi Giang,

Without seeing any of your code this is going to be hard to solve :-)

Comment by: Giang Pham (19-12-2016 10:37:07) deeplink to this comment

Comment by: Jan Karel Pieterse (19-12-2016 12:09:26) deeplink to this comment

Hi

For the undohandler to work you must use it also to make the changes that later need to be undone. See the example on the previous page of this article where I use "mUndoClass.AddAndProcessObject" to change a property and also add it to the undo stack.

Comment by: Giang Pham (20-12-2016 10:43:13) deeplink to this comment

Thank you for your suggestion, I will investigate further on your undohandler code :)

Comment by: Giang Pham (21-12-2016 04:04:30) deeplink to this comment

Hi Jan,
I was able to undo the highlight_dup thanks to your hint.
When investigating the mUndoClass.AddAndProcessObject, I notice this:
Function AddAndProcessObject(oObj As Object, sProperty As String, vValue As Variant)

Does this mean this Undo only works on changing Objects's property? What if I want to record the changes when I used methods of the object?

Thank you for your time.
You have been very helpful!

Comment by: Jan Karel Pieterse (21-12-2016 09:48:29) deeplink to this comment

Hi,

What method are you considering to use? Indeed doing things like deleting a range of cells cannot be handled by this undo handler. That would require some serious programming as you would have to somehow store what the original (to be deleted) range of cells contained. Also, you would have to write all of the code to put everything back in place.

Comment by: Giang Pham (21-12-2016 10:20:27) deeplink to this comment

Yeah, i want to delete a range of cells. As you say, there is no easy way to undo that.
Thank you for your time!

Comment by: ZC (22-8-2017 15:15:40) deeplink to this comment

Hi

Would this work on the following code?

Comment by: Jan Karel Pieterse (22-8-2017 16:08:00) deeplink to this comment

Hi Zara,

Theoretically, yes. See the previous page of this article for an example:

https://jkp-ads.com/Articles/UndoWithVBA03.asp

Comment by: warbe (17-7-2018 19:47:36) deeplink to this comment

I have undertaken the same effort by means of an undo class module which is not only very simple to use but also requires minimum code change.
BeginOfTransaction starts a new collection of changed cells
ChangeInTransaction provided with one or more cells about to be changed saves each cells properties (content, font, and interior)
EndOfTransaction prepares for undo (activates the undo icon)
BackoutTransaction sets all saved cells into their state prior the change. This procedure is invoked through the undo icon by a simple public procedure called OpUnDo.

I am about to publish this implementation here if you dont'mind

Comment by: Jan Karel Pieterse (18-7-2018 10:34:24) deeplink to this comment

Hi Warbe,

Sounds interesting. I expect this is a lot of code, given that there are a lot of properties to consider!

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.

Comment by: Chris (28-3-2024 20:25:00) deeplink to this comment

How would I implement your code with this?:

Comment by: Jan Karel Pieterse (29-3-2024 10:30:00) deeplink to this comment

Hi Chris,

Depends; what exactly do you want to enable the user to Undo?

Comment by: Gary (2-10-2024 23:38:00) deeplink to this comment

Thank you!

Comment by: Vinicius Bubiak (3-10-2024 15:44:00) deeplink to this comment

Hey Jean, Im trying to implement your logic to undo macro painted cells and writting (using a button in a custom ribbon tab). Still working on the painting side. Facin some issues when implementing it:

After i run my painting macro i get this in instant window:

Object added to Undo stack, total objects:             3
Old value retrieved:         16777215
New value set:             14849448
ExecuteCommand succeeded
Undo count:                3
Undoing all actions, total count:         3
Undoing object             3             with old value:             16777215     Type:        clsUndoObject
Accessing property: Interior on object of type: Range


After this i get an erro box:
Runtime error 438
The object doesn't accept this property or method

when debuggin it point to --> mUndoChange.UndoAll when using Undochange and i get mUndoChange.Last when using UndoStepwise

Comment by: Jan Karel Pieterse (7-10-2024 10:39:00) deeplink to this comment

Hi Vinicius,

Apparently you have added some debug.print statements to the code. Perhaps you could send the file to me (see email address below) and outline the steps I need to take to reproduce your problem?

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.