Hi baig123,
Unfortunately, what you want probably can not be done easily.
Take the following example Macro:
Sub SimpleUndo()
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub
For example if you put 1,2,3,4 in 4 consecutive cells. Manually you can undo all 4 actions.
Put 1,2,3,4 in 4 consecutive cells again. Run Macro SimpleUndo() several times. All that happens is the 4 toggles between 4 and whatever was in the cell previously.
You can write a macro to log changes you make (i.e. saving the previous value). You can then write another macro to undo what was done by using the values of the changes (i.e. previous values) you saved. See the attached file that contains the following code:
Sheet1 module:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call SavePreviousValue(Target)
End Sub
Ordinary Code module (e.g. Module ModUnDo):
Option Explicit
Public Type myType
sAddress As String
vValue As Variant
End Type
Public myGblVarrayCount As Long
Public myGblVarray() As myType
Sub SimpleUndo()
'This will undo the last value changed
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub
Sub ClearCustomUndoArray()
myGblVarrayCount = 0
ReDim myGblVarray(1 To 1)
End Sub
Sub MyCustomUndo()
'This will undo all stored changes
Dim vValue As Variant
Dim i As Long
Dim sAddress As String
Application.EnableEvents = False
If myGblVarrayCount > 0 Then
For i = myGblVarrayCount To 1 Step -1
sAddress = myGblVarray(i).sAddress
vValue = myGblVarray(i).vValue
Range(sAddress) = vValue
Next i
Call ClearCustomUndoArray
End If
Application.EnableEvents = True
End Sub
Sub SavePreviousValue(ByVal Target As Range)
'This will not work if more than one value is changed at a time
'
'This is the Event Handler called by the Worksheet_Change() event
Dim myValue As Variant
If Target.Count > 1 Then
Exit Sub
End If
'Get the current value
myValue = Target.Value
'Save the previous value for later use
Application.EnableEvents = False
Application.Undo
myGblVarrayCount = myGblVarrayCount + 1
If myGblVarrayCount = 1 Then
ReDim myGblVarray(1 To myGblVarrayCount)
Else
ReDim Preserve myGblVarray(1 To myGblVarrayCount)
End If
myGblVarray(myGblVarrayCount).sAddress = Target.Address(False, False) '(false,false) means NO '$' signs
myGblVarray(myGblVarrayCount).vValue = Target.Value
'Restore the new value
Target.Value = myValue
'Set the focus one cell to the right of the value just changed
Range(Target.Address).Offset(0, 1).Select
Application.EnableEvents = True
End Sub
Lewis
Bookmarks