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

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

 


Comment by: Marvin (1/29/2016 11:59:14 PM) Direct link 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 (1/30/2016 1:47:13 PM) Direct link to this comment

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) Direct link 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 (2/1/2016 9:54:48 AM) Direct link 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 (2/5/2016 7:04:16 PM) Direct link 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 (2/8/2016 1:53:30 PM) Direct link 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 (3/7/2016 7:48:40 AM) Direct link 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 (3/7/2016 9:19:23 AM) Direct link 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.

 


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].