B
bradmcq
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
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