[SOLVED] UNDO in VBA code???

Okay, I wrote this really cool function, you see. It populates tons of blank
cells for the end-users, saving them hour upon hours of time. Everyone's
happy....except....

The function updates a bunch of data, but the "Undo" button is never
activated. So, if the want to undo the data changed by my way-cool function,
the only way for them to do it is to close the file without saving changes
and re-open it.

How do I get my code to activate the "Undo" button, so the changes can be
rolled back if needed?

Thanks!

Joel,

When you run a procedure, the Undo buffer is cleared; previous
commands cannot be undone. You can create your own Undo
procedure, but when you go to Undo in the Excel UI, only this
procedure is available. Use Application.OnUndo to assign a
procedure to the Undo action. E.g.,

Public Sub AAA()
Application.OnUndo "Undo This", "UndoProc"
' your code here
End Sub

Sub UndoProc()
' code to undo
End Sub


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


"Joel" <Joel@discussions.microsoft.com> wrote in message
news:14265702-F815-4AE3-8030-3529F5755900@microsoft.com...
> Okay, I wrote this really cool function, you see. It populates
> tons of blank
> cells for the end-users, saving them hour upon hours of time.
> Everyone's
> happy....except....
>
> The function updates a bunch of data, but the "Undo" button is
> never
> activated. So, if the want to undo the data changed by my
> way-cool function,
> the only way for them to do it is to close the file without
> saving changes
> and re-open it.
>
> How do I get my code to activate the "Undo" button, so the
> changes can be
> rolled back if needed?
>
> Thanks!

Simple answer, macros can not be undone, you'll have to write a roll back
feature into your macro.
ben

"Joel" wrote:

> Okay, I wrote this really cool function, you see. It populates tons of blank
> cells for the end-users, saving them hour upon hours of time. Everyone's
> happy....except....
>
> The function updates a bunch of data, but the "Undo" button is never
> activated. So, if the want to undo the data changed by my way-cool function,
> the only way for them to do it is to close the file without saving changes
> and re-open it.
>
> How do I get my code to activate the "Undo" button, so the changes can be
> rolled back if needed?
>
> Thanks!

Save the cells before the cool function kicks in, and provide a facility
that re-instates the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joel" <Joel@discussions.microsoft.com> wrote in message
news:14265702-F815-4AE3-8030-3529F5755900@microsoft.com...
> Okay, I wrote this really cool function, you see. It populates tons of
blank
> cells for the end-users, saving them hour upon hours of time. Everyone's
> happy....except....
>
> The function updates a bunch of data, but the "Undo" button is never
> activated. So, if the want to undo the data changed by my way-cool
function,
> the only way for them to do it is to close the file without saving changes
> and re-open it.
>
> How do I get my code to activate the "Undo" button, so the changes can be
> rolled back if needed?
>
> Thanks!

John Walkenbach has some tips, too:
http://www.j-walk.com/ss/excel/tips/tip23.htm


Joel wrote:
>
> Okay, I wrote this really cool function, you see. It populates tons of blank
> cells for the end-users, saving them hour upon hours of time. Everyone's
> happy....except....
>
> The function updates a bunch of data, but the "Undo" button is never
> activated. So, if the want to undo the data changed by my way-cool function,
> the only way for them to do it is to close the file without saving changes
> and re-open it.
>
> How do I get my code to activate the "Undo" button, so the changes can be
> rolled back if needed?
>
> Thanks!

--

Dave Peterson