Worksheet_Change
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
|