Can't Undo Macro Command

[TKS_Mark]

After I run the command below, I can't undo. What change should I make to
make it undo-able.

Thanks.

Sub CopyFormula()

Dim myRng As Range

'single area at a time
Set myRng = Selection.Areas(1)

With Intersect(myRng.EntireRow, ActiveSheet.Range("r1").EntireColumn)
.Copy
With .Offset(0, -12)
.PasteSpecial Paste:=xlPasteValues
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With
Application.CutCopyMode = False
End Sub

 

[Jim Thomlinson]

There is no way to undo a macro... Once executed there is no way back.

 

[TKS_Mark]

Ouch!

There is no way to undo a macro... Once executed there is no way back.

 

[Dave Peterson]

You can read some notes from John Walkenbach here:
http://j-walk.com/ss/excel/tips/tip23.htm

 

[Jim Thomlinson]

The closest you will get is to write a macro that reverses what you
originally did. So long as you have copies of things before they were changed
you can write a macro to put things back the way you found them...

 

[TKS_Mark]

Ok, that looks very helpful. I ran the sample program in a test file and it
worked great. Then I modified it for my purposes as below. I moved the
For-Next loop to after the offset but before the paste. I removed the Option
Explicit statement from my code because I don't know how to declare the i
variable correctly.

The trouble is, when I run the code, I still can't undo. Do you see any
errors below? How should I declare the i so I can still use Option Explicit?

'Custom data type for undoing
Type SaveRange
Val As Variant
Addr As String
End Type

' Stores info about current selection
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange
Sub CopyFormula()

' Abort if a range isn't selected
If TypeName(Selection) <> "Range" Then Exit Sub

Dim myRng As Range

'single area at a time
Set myRng = Selection.Areas(1)

With Intersect(myRng.EntireRow, ActiveSheet.Range("r1").EntireColumn)
.Copy
With .Offset(0, -12)

' The next block of statements
' saves the current values for undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).Addr = cell.Address
OldSelection(i).Val = cell.Formula
Next cell

.PasteSpecial Paste:=xlPasteValues
Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With
Application.CutCopyMode = False
' Specify the Undo Sub
Application.OnUndo "Undo the CopyFormula macro", "UndoCopyFormula"
End Sub


Sub UndoCopyFormula()
' Undoes the effect of the CopyFormula sub

' Tell user if a problem occurs
On Error GoTo Problem

Application.ScreenUpdating = False

' Make sure the correct workbook and sheet are active
OldWorkbook.Activate
OldSheet.Activate

' Restore the saved information
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Next i
Exit Sub

' Error handler
Problem:
MsgBox "Can't undo"
End Sub

 

[Dave Peterson]

Dim i as long
(since it's a counter of cells)

But you have other stuff to fix. John's code worked on the Selection. But you
want it to work on columns F:G and for as many rows as you had selected.

This seemed to work in light testing:

Option Explicit
'Custom data type for undoing
Type SaveRange
Val As Variant
Addr As String
End Type

' Stores info about current selection
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange


Sub CopyFormula()

Dim myRngToCopy As Range
Dim myRng As Range
Dim i As Long
Dim cell As Range

' Abort if a range isn't selected
If TypeName(Selection) <> "Range" Then Exit Sub

'single area at a time
Set myRng = Selection.Areas(1)
Set myRngToCopy = Intersect(myRng.EntireRow, _
ActiveSheet.Range("r1").EntireColumn)

ReDim OldSelection(myRngToCopy.Offset(0, -12).Resize(, 2).Cells.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet

i = 0
For Each cell In myRngToCopy.Offset(0, -12).Resize(, 2).Cells
i = i + 1
OldSelection(i).Addr = cell.Address
OldSelection(i).Val = cell.Formula
Next cell

With myRngToCopy
.Copy
With .Offset(0, -12)
.PasteSpecial Paste:=xlPasteValues
'Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With
Application.CutCopyMode = False
' Specify the Undo Sub
Application.OnUndo "Undo the CopyFormula macro", "UndoCopyFormula"
End Sub
Sub UndoCopyFormula()
' Undoes the effect of the CopyFormula sub

Dim i As Long

' Tell user if a problem occurs
On Error GoTo Problem

Application.ScreenUpdating = False

' Make sure the correct workbook and sheet are active
OldWorkbook.Activate
OldSheet.Activate

' Restore the saved information
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Next i
Exit Sub

' Error handler
Problem:
MsgBox "Can't undo"
End Sub

 

[Rick S.]

On critical data the first thing I do is copy the sheet or range or cell to
another sheet or workbook. At the very least I can copy and paste the old
data back the way it was. Becarefull with Hyperlinks in Excel 2007!
On re-occurring macro's I simply over write the backed up data. If the last
edit I made via a macro was wrong I can go back.

Not as fancy as the guru's but it saved me many times.
Not that I need saving all the time!
:Looks left: :Looks right:

HTH
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[TKS_Mark]

Mr. Peterson: As always, that was very helpful. I hate to push my luck, but
I have one more question.

I added...

Selection.Style = "Calculation"

just after the last End With. I guessed that I could store the previous
format using...

OldSelection(i).Val = cell.FormatConditions

inside the for-next loop and at undo restore the stored format by modifying
a line to...

Range(OldSelection(i).Addr).Formula.FormatConditions = OldSelection(i).Val

But Excel didn't like my first store statement.

Thanks!

 

[Dave Peterson]

If you know that the style will always be calculation, you don't need to store
it.

Add a line under:
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Range(OldSelection(i).Addr).style = "Calculation"

(untested)

If you were writing a generic Undo procedure, you'd have to keep track of the
style (or other formatting you want).

Mr. Peterson: As always, that was very helpful. I hate to push my luck, but
I have one more question.

I added...

Selection.Style = "Calculation"

just after the last End With. I guessed that I could store the previous
format using...

OldSelection(i).Val = cell.FormatConditions

inside the for-next loop and at undo restore the stored format by modifying
a line to...

Range(OldSelection(i).Addr).Formula.FormatConditions = OldSelection(i).Val

But Excel didn't like my first store statement.

Thanks!

 

[TKS_Mark]

My new format will always be calculation, but I'm wanting to be able to redo
it to the old font, pattern, border, etc.

 

[Dave Peterson]

If applying the Style doesn't give you want you want, you're going to have to
keep track of everything you changed that you want to get back.
FontSize as double
FontName as String
'all this stuff
And you'll have to save it in this portion:
oldselection(i).FontName = cell.font.name
oldselection(i).fontsize = cell.font.size
It's gonna be a real pain if you use different sized fonts and different fonts
within the cell!

But you're pasting values--not changing the format. I guess I don't understand
why the formatting is changing.

My new format will always be calculation, but I'm wanting to be able to redo
it to the old font, pattern, border, etc.