Undo functionality w/ VBA?

[Kevin T. Ryan]

Hi all -

I was wondering if anyone knew of a way to save the state of an excel
workbook so that users could Ctrl-Z (i.e. undo) changes made by a VBA
procedure or macro? For example, if my macro simply bold-faced a
particular cell, Ctrl-Z would revert the formatting to whatever it was
before? Any thoughts would be appreciated. TIA,

Kevin

 

[Chip Pearson]

Kevin,

Running a VBA procedure clears Excel's undo buffer, effectively
disabling the Undo feature. The closest you can get is to create
a procedure that undoes your primary procedure, and use
Application.OnUndo to put that procedure in the undo buffer.
E.g.,

Sub AAAA()
Application.OnUndo "Undo This", "UndoProc"
End Sub

Sub UndoProc()
MsgBox "Undo code here"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[TroyW]

Kevin,

As Chip explained there isn't any built-in method. If you really need this
type of functionality, then one general approach might be to save a current
copy of the workbook before starting the VBA changes. If you need to revert
back, then close the current workbook and open the previously saved file.

Troy

 

[Kevin T. Ryan]

Thanks guys, I'll probably try the "save-changes", re-open procedure
as it sounds a little easier to implement. Take care,

Kevin