Undo custom macro action

[bjones]

When I run a custom macro that I have created, the undo icon is not available to undo the actions. Is there a way to allow the undo function to work for custom macros. Thanks.

 

[Joe4]

I am pretty sure the Undo function is not available for any macros / VBA Code. That is why you often see people put code on their macros and VBA Code that says "Are you sure you want to do this?".

 

[Haluk]

The Undo buton will have no effect for the changes made by the macro, unless you use some macro codes.

Here is a sample code to restore the changes made by the macro (EditRange). Just select a range on your worksheet and run the macro (EditRange).

Sub EditRange()
Dim i As Integer, cl As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False
ReDim OrgCells(Selection.Count)
Set OrgWB = ActiveWorkbook
Set OrgWS = ActiveSheet
i = 1
For Each cl In Selection
OrgCells(i).CellContent = cl.Formula
OrgCells(i).CellAddress = cl.Address
i = i + 1
Next cl
Selection.Formula = "The selected cells have been filled by the macro, now you can click the Undo button to restore !"
Application.OnUndo "Undo the latest macro", "UndoEditRange"
End Sub

Sub UndoEditRange()
Dim i As Integer
Application.ScreenUpdating = False
On Error GoTo NoWBorWS
OrgWB.Activate
OrgWS.Activate
On Error GoTo 0
For i = 1 To UBound(OrgCells)
Range(OrgCells(i).CellAddress).Formula = OrgCells(i).CellContent
Next i
Set OrgWB = Nothing
Set OrgWS = Nothing
Erase OrgCells
NoWBorWS:
End Sub

 

[stevebausch]

Search the Object Browswer on 'Undo', if you can craft a routine to undo another macro, you are halfway there. You just need to tell Excel about it.

 

[Eric Cotton]

hi there guys.
Im not really fermilliar with VBA coding. And I eg delete a line thus causing the macro run. Then realise that the line I deleted had important information and thus due to the macro can't use CNTRL Z to restore the data that was deleted.

What should I do, to add to this code.

If I have a VBA code, eg.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 1 Then
Target.EntireRow.Select
Target.Activate
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

Dim StartNum As Integer
Dim FirstCell As Integer
Dim LastCell As Integer

StartNum = 1
FirstCell = 3
LastCell = 1115

Application.EnableEvents = False
Do While FirstCell <= LastCell
Range("A" & FirstCell).Value = StartNum
FirstCell = FirstCell + 1
StartNum = StartNum + 1
Loop
Range("A" & LastCell + 1).Value = ""
Application.EnableEvents = True

End Sub