Worksheet_Change
Create a module level variable, and on the SelectionChange event save the
Target.value to that variable. When you enter the Change event you will have
access to the previous value in that variable.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"C. Öhreneder" wrote in message
...
Hi!
I have written a macro which is intended for tracking changes in a table.
If
a cell is changed the date of change and the user name are written to
particular columns in the same row.
My Problem is to support Undo functionality. How is that done best??
In my current code I undo the change to remember the old value of the cell
and then set it to the new value again.
Is there a better method?
How can I know the old value of a cell within the procedure
Worksheet_Change?
Any hints how to handle that???
Thanks a lot!
Christian
Dim not_now As Integer
Dim ChangedRange As Object
Dim LastValue As Variant
Dim NewValue As Variant
Dim LastTime As Variant
Dim LastUser As Variant
Const TimeCol = 4
Const UserCol = 5
Private Sub Worksheet_Change(ByVal Ziel As Range)
If (Ziel.Column < TimeCol) And (Ziel.Column < UserCol) And (not_now
=
0) Then
not_now = 1
Set ChangedRange = Ziel
NewValue = Ziel.Value //
remember the new value
Application.Undo // undo
the cange
R = Ziel.Row
LastValue = Ziel.Value // rember
the old value
LastTime = ActiveSheet.Cells(R, TimeCol).Value
LastUser = ActiveSheet.Cells(R, UserCol).Value
Ziel.Value = NewValue // redo the
change
ActiveSheet.Cells(R, TimeCol).Value = Date + Time()
ActiveSheet.Cells(R, UserCol).Value = Application.UserName
Application.OnUndo "Rückgängig: Change + Date + User",
ActiveSheet.CodeName + ".myundo"
Application.OnRepeat "Wiederholen: Change + Date + User",
ActiveSheet.CodeName + ".myrepeat"
not_now = 0
End If
End Sub
Sub myundo()
not_now = 1
R = ChangedRange.Row
ChangedRange.Value = LastValue
ActiveSheet.Cells(R, TimeCol).Value = LastTime
ActiveSheet.Cells(R, UserCol).Value = LastUser
not_now = 0
End Sub
Sub myrepeat()
not_now = 1
R = ChangedRange.Row
ChangedRange.Value = NewValue
ActiveSheet.Cells(R, TimeCol).Value = Date + Time()
ActiveSheet.Cells(R, UserCol).Value = Application.UserName
Application.OnUndo "Rückgängig: Change + Date + User",
ActiveSheet.CodeName + ".myundo"
Application.OnRepeat "Wiederholen: Change + Date + User",
ActiveSheet.CodeName + ".myrepeat"
not_now = 0
End Sub
|