Conclusion

JKP Application Development Services.

Microsoft Office Application Development

Conclusion

Up • Class Modules (1) • Class Modules (2) • Implementation • Conclusion

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

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.

You may also rate this article:

(Rated: 245 times, average rating: 5.9)

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 =

you could parse this as

mUndoClass.AddAndProcessObject object1.object2.object3,"property1",

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"

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

Add a comment too!!!

Subscribe in a reader