Undo a macro?

[stone]

Is it possiple to undo a macro.

I have created a button in my worksheet,if you push it a userform pops up, this userform has two buttons, if you push button1 then it clear contents in some cells and then the userform closes, if you push button2 then the userform closes.

see code below....

Private Sub button1_click()
Dim r1 As Range, r2 As Range, r3 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("B13:H111")
Set r2 = Range("K13:N111")
Set r3 = Range("P13:S111")
Set myMultiAreaRange = Union(r1, r2, r3)
myMultiAreaRange.ClearContents
userform.Hide
End Sub

Private Sub button2_Click()
userform.Hide
End Sub


What i need to do is to make a another button on my worksheet, that undo the last action that was made, including the userform macro (see code above)(that is it must be able to undo the macro, if that was the last action)

Is this possible to do?

BK
Stulli

[Mark O'Brien]

There is no builtin method of Undoing the actions of a macro.

It is possible to workaround this. What you will need to do before the macro is run is to make a copy of the workbook in a temp folder or something. Then, when you run the "Undo" macro you simply need to load the copy of the folder, thus giving the illusion of undoing the last action.

(I say "simply", but I would have a search for this sort of thing on this site or even do a web search to see if anyone's got a nice example of this)

HTH

[WayneTN]

Stone,

This won't be much help since I don't use VBA that much, but I have some macro buttons on a worksheet that I update daily. Some of my code does a time calculation and tells me how many times to press one of the buttons (I know, crude, but it works). In case I or someone else presses the update button too many times - planning for everything - I actually have an Un-update button that reverses the last update. This is real simple, but somewhat related. Hopefully someone with more experience can tell you how to do this correctly......Wayne

[Ricky Morris]

What you need to do is store the value of the cells that are cleared by the button as soon as the form is loaded. Then if the Undo button is pressed, restore the contents you saved. If you need any further assistance let me know.

You may not need to get as complicated as this but here's an example of how I undo formatting that I have attached to custom buttons on my toolbar.

Type SaveRange
Underline As Variant
Bold As Boolean
Addr As String
Style As Variant
H_Alignment As Variant
Val As Variant
Num_Format As Variant
End Type
Sub CenterAcrossSelection()
' this code is executed when the Center Across Selection
' button is pressed.
On Error Resume Next
Call Save_Undo_Info
Selection.HorizontalAlignment = xlCenterAcrossSelection
Application.OnUndo "Center Alignment", "UndoMyMacro"
' The first argument "Center Alignment" will be displayed
' in the drop down list of the undo button.
' The second argument is the name of the Macro that will
' undo the changes.
End Sub
Sub UndoMyMacro()
On Error GoTo ErrorHanlder
Dim i As Integer
Application.ScreenUpdating = False
OldWorkbook.Activate
OldSheet.Activate
For i = 1 To UBound(OldSelection)
With Range(OldSelection(i).Addr)
.Font.Underline = OldSelection(i).Underline
.Font.Bold = OldSelection(i).Bold
.Style = OldSelection(i).Style
.HorizontalAlignment = OldSelection(i).H_Alignment
.Formula = OldSelection(i).Val
.NumberFormat = OldSelection(i).Num_Format
End With
Next i
Exit Sub
ErrorHanlder:
MsgBox "Can't Undo", vbCritical
End Sub
Sub Save_Undo_Info()
Dim cell As Range
Dim i As Integer
If TypeName(Selection) <> "Range" Then Exit Sub
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
With OldSelection(i)
.Addr = cell.Address
.Underline = cell.Font.Underline
.Bold = cell.Font.Bold
.Style = cell.Style
.H_Alignment = cell.HorizontalAlignment
.Val = cell.Formula
.Num_Format = cell.NumberFormat
End With
Next cell
End Sub

[stone]

Thanx alot guys.

BK
Stulli