Can I support Undo?

I have a button that does x,y and z to the worksheet. User clicks the
button, decides it was a mistake and wants to 'undo'.

It could be so easy -
Application.SaveUndoPoint("Execute Macro: MyMacro")

at the end of the macro.

Waiting to be disappointed,

Matthew

Hopefully you didn't wait too long. No, there is no easy way to do this. You
would have to write your own custom routine, which would involve
"remembering" the state of the relevant parts of the workbook.

Regards,

Vasant.

"Matthew Connor" <mco__DONTNEEDTHIS__nnor1600@YA__DONTNEEDTHIS__HOO.com>
wrote in message news:10564057...@nntp.acecape.com...

Thanks,

Matthew

<snipped>

Before the macro does anything else, copy the 'live' worksheet to a
worksheet you name "Temp"
Run the rest of your macro on the 'live' sheet
If everything is OK the "temp" sheet will be overwritten the next time the
macro is run.
If there was an errror and you want to go back (Undo) what the macro has
done,
write another macro to copy "Temp" back to the 'live' worksheet.
Set this up on a button marked "Undo".

Another way is for your macro to work on the "temp" sheet and to copy it
over the 'live' sheet if
the user hits a button named "Save"

HTH
Henry

Note: Also remember that Excel's "AutoRecover" feature also wipes out the
undo history.

Sub Undo_Activate()
[A1] = "One"
[A2] = "Two"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.FullName, _
AccessMode:=xlShared

With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = False
.HighlightChangesOnScreen = True
End With
End Sub

Sub Make_Some_Changes_With_VBA()
' = = = = = = = = = =
' Do some changes manually...
' = = = = = = = = = =
' VBA won't record the following, but this
' is just something to add...
' Shows 1 way to "Undo" VBA changes
' = = = = = = = = = =
[A1:D20] = "Lots of Changes"
[A:D].Columns.AutoFit
' ...and kill the undo button
ActiveWorkbook.Save
End Sub

Sub Undo()
'// Dana DeLouis
' Try to go back...

Application.DisplayAlerts = False
ActiveWorkbook.RejectAllChanges
ActiveWorkbook.ExclusiveAccess
End Sub

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Matthew Connor" <mco__DONTNEEDTHIS__nnor1600@YA__DONTNEEDTHIS__HOO.com>
wrote in message news:10564118...@nntp.acecape.com...

Matthew

<snip>
>

Thanks Dana. It does come closest to setting pure Undo save-points.

Being a moody-loner, I'm not a user of Track Changes -- I doubt this
idea would have occurred to me. It certainly is a cool idea. My users
are not very sophisticated (don't use Track Changes)- the changes
apparent in the application from the Shared/Track Changes would just
cause too much confusion. I don't think I want to open up that can of
worms.

I could imagine using this for something only I (or a few
well-informed users) was going to be using.

Thanks,

Matthew

Examples below: UndoDeleteRow would not work if Macro1
was previously used, however, it would work if DeleteRow
was previously used.

Sub Macro1()
Selection.EntireRow.Delete
End Sub

Sub DeleteRow()
SendKeys ("%EDR~")
End Sub

Sub UndoDeleteRow()
SendKeys ("%EU")
End Sub

Hope this helps.