Undo custom macro action

bjones

New Member
Joined
Oct 28, 2002
Messages
1
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,558
Office Version
  1. 365
Platform
  1. Windows
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

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

New Member
Joined
Aug 25, 2015
Messages
9
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
 

Forum statistics

Threads
1,144,741
Messages
5,726,016
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top