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

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