Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub Worksheet_Change | Excel Discussion (Misc queries) | |||
worksheet_Change | Excel Programming | |||
Worksheet_Change DDE | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |