Hi
I have the code below to insert a worksheet formula into cells within the
range G24:G35 of the active sheet, and to allow the user to undo if the
inadvertently delete the contents of a cell.

Type SaveRange
Val As Variant
Addr As String
End Type


Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange


Sub undoChange()



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
OldSelection(i).Addr = cell.Address
OldSelection(i).Val = cell.Formula
Next cell


Set r = Range("G24:G217")
On Error Resume Next
For Each cell In r
If cell = 0 Then
cell.FormulaR1C1 = "=IF(COUNTIF(Rc18:RC25,""Y"")0,""Objective
required"","""")"

End If
Next cell



Application.OnUndo "Undo the ZeroRange macro", "UndoZero"
End Sub


Sub UndoZero()

On Error GoTo Problem

Application.ScreenUpdating = False


OldWorkbook.Activate
OldSheet.Activate


For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Next i
Exit Sub


Problem:
MsgBox "Can't undo"
End Sub


It seems to work if I run the macro by selecting the range G24:G35 and
manually run the macro, but if I run it from an a worksheet_change event I
get an error message related to the follownig line.

ReDim OldSelection(Selection.Count)

I can't work out why executing form the event handling of the sheet is a
problem

This is the chnage event code I am using
Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("G24:G35")) Is Nothing Then
Range("G24:G35").Select
End If
Call undoChange

End Sub

Any help would be much appreciated