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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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 60 in total (Show All Comments):

 


Comment by: Giang Pham (21-12-2016 10:20:27)

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)

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 (22-8-2017 16:08:00)

Hi Zara,

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

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

 


Comment by: warbe (17-7-2018 19:47:36)

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)

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)

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)

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)

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.

 


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, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.