Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Undo With Excel VBA > Conclusion
Deze pagina in het Nederlands

Creating An Undo Handler To Undo Changes Done By Excel VBA

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

All comments about this page:


Comment by: DM Unseen (3/9/2006 6:25:32 AM)

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 (3/9/2006 6:30:22 AM)

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

 


Comment by: Jan Karel Pieterse (3/9/2006 8:37:59 AM)

Hi DM, Good suggestions!

 


Comment by: Vyacheslav Maliuhin (3/2/2007 5:55:22 AM)

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 (3/2/2007 7:31:57 AM)

Hi Vyacheslav,

I agree with that. And thanks for the link.

 


Comment by: Jac (12/6/2007 2:09:52 AM)

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 (12/6/2007 3:12:30 AM)

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 (4/10/2008 3:04:38 AM)

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 (4/10/2008 5:23:16 AM)

Hi So,

I'm sorry, that is beyond my expertise.

 


Comment by: andrewit (6/27/2008 1:46:39 AM)

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 (4/5/2009 12:13:36 PM)

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 (4/6/2009 12:51:19 AM)

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 (11/10/2010 6:14:23 AM)

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 (11/10/2010 6:43:37 AM)

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 (1/15/2011 4:19:16 AM)

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 (1/15/2011 11:14:41 AM)

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

 


Comment by: Sir Shagsalot (1/26/2011 8:46:14 PM)

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 (10/19/2011 12:38:13 PM)

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 (10/19/2011 11:20:42 PM)

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 (5/9/2012 1:13:46 PM)

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 (5/9/2012 11:17:14 PM)

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 (8/7/2012 10:49:53 PM)

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 1:15:28 PM)

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 (2/26/2013 3:11:08 PM)

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 (2/26/2013 3:35:04 PM)

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 (2/26/2013 3:58:26 PM)

Hi Jan,

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

Application.OnUndo "Undo Coloring", "UndoChange"

I can click on the nice little button in the upper left corner to do what i defined in "UndoChange".

I wrote Application.OnUndo "Undo Coloring", "UndoChange" at the end of the Sub "UndoChange" so that i could repeatedly execute that macro via the undo button.

It doesn't take it. The button stays gray after one use.

Only when i execute the "UndoChange" macro manually step by step (F8) will it reenable the undo button.

I couldn't find anything in the internet... only people with the same question but no answer anywhere.

Hope you can help me there?

 


Comment by: Jan Karel Pieterse (2/26/2013 4:37:13 PM)

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:

Sub UndoStepwise()
    If mUndoClass Is Nothing Then Exit Sub
    mUndoClass.UndoLast
    If mUndoClass.UndoCount = 0 Then
        MsgBox "Last action undone"
        Set mUndoClass = Nothing
    End If
    Application.OnTime Now, "AddUndo"
End Sub

Sub AddUndo()
    Application.OnUndo "Undo Something", "UndoStepwise"
End Sub

 


Comment by: Florian (2/27/2013 8:30:57 AM)

Hi Jan,

Many many many thanks! I would never come up with
application.ontime now


you saved me a lot of time and trouble :)

Greetings

 


Comment by: Jan Karel Pieterse (2/27/2013 10:29:37 AM)

Hi Florian,

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

 


Comment by: Milind (6/17/2013 11:16:29 AM)

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

 


Comment by: Jan Karel Pieterse (6/17/2013 11:28:01 AM)

Hi Milind,

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

 


Comment by: Ivan Lanin (10/5/2013 7:08:58 PM)

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 (8/13/2015 2:29:45 PM)

Simply Awesome!

 


Comment by: Marvin (1/29/2016 8:13:50 PM)

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 (1/29/2016 8:58:32 PM)

Hi Marvin,

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

Sheets("MADRE").Cells(Target.Row, 7).Value = Now

 


Comment by: Marvin (1/29/2016 11:59:14 PM)

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 (1/30/2016 1:47:13 PM)

Hi Marvin,

In a normal module, paste this code:


Public gvPreviousValue As Variant
Public goModifiedCells As Range

Sub UndoTimeEdit()
    If not goModifiedCells Is Nothing Then
        goModifiedCells.Value = gvPreviousValue
        Set goModifiedCells = Nothing
    End If
End Sub


In your event routine:

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
        gvPreviousValue = Sheets("MADRE").Cells(Target.Row, 7).Value
        Set goModifiedCells = Sheets("MADRE").Cells(Target.Row, 7)
        Sheets("MADRE").Cells(Target.Row, 7).Value = Now
        Application.OnUndo "Undo time stamp", "UndoTimeEdit"
    End If
End Sub

 


Comment by: Marvin (1/31/2016 6:38:09 PM)

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 (2/1/2016 9:54:48 AM)

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 (2/5/2016 7:04:16 PM)

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 (2/8/2016 1:53:30 PM)

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 (3/7/2016 7:48:40 AM)

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 (3/7/2016 9:19:23 AM)

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 (12/7/2016 7:15:39 PM)

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 (12/8/2016 7:00:48 AM)

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 (12/19/2016 9:47:14 AM)

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 (12/19/2016 10:17:50 AM)

Hi Giang,

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

 


Comment by: Giang Pham (12/19/2016 10:37:07 AM)


' Highlight duplicate value
Sub highlight_dup(control As IRibbonControl)
    Application.ScreenUpdating = False
    
    Dim o As Range
    
    If mUndoClass Is Nothing Then
        Set mUndoClass = New clsExecAndUndo
    Else
        'Previous undoset, must be removed
        Set mUndoClass = Nothing
        Set mUndoClass = New clsExecAndUndo
    End If
    
    For Each o In Selection
        If Range(Selection.Item(1).Address & ":" & o.Address).Find(o.Value).Address <> o.Address Then
            With o.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next o
    
    Application.OnUndo "Restore colours A1:A10", "Undo_highlight_dup"
    Application.ScreenUpdating = True
End Sub

Sub Undo_highlight_dup()
    If mUndoClass Is Nothing Then Exit Sub
    mUndoClass.UndoAll
    Set mUndoClass = Nothing
End Sub

These are the sub and the corresponding undo sub in .xlam file.
I already created 2 classes: clsExecAndUndo and clsUndoObject as you suggested

 


Comment by: Jan Karel Pieterse (12/19/2016 12:09:26 PM)

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 (12/20/2016 10:43:13 AM)

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

 


Comment by: Giang Pham (12/21/2016 4:04:30 AM)

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 (12/21/2016 9:48:29 AM)

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 (12/21/2016 10:20:27 AM)

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 (8/22/2017 3:15:40 PM)

Hi

Would this work on the following code?
Sub Macro2()

Dim objCht As ChartObject
Dim ws As Worksheet

' loop through sheets in this workbook
For Each ws In ThisWorkbook.Worksheets
    ' loop through Chartobjects in sheet
    For Each objCht In ws.ChartObjects
        ' no need to select the chart or the series use With statement instead
        With objCht.Chart.SeriesCollection(1)
         .HasErrorBars = True
            With .ErrorBars.Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
                .ForeColor.TintAndShade = -0.0500000119
                .ForeColor.Brightness = 0
                .Transparency = 1
            End With
        End With
    Next objCht
Next ws

End Sub

 


Comment by: Jan Karel Pieterse (8/22/2017 4:08:00 PM)

Hi Zara,

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

http://www.jkp-ads.com/Articles/UndoWithVBA03.asp

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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