Most Valuable Professional


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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
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 32 in total (Show All Comments):

 


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.

 


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