Show last date a cell was modified

[AlexSrois]

Hi Guys!

I have a table in which the A column indicate the status of a task and the other columns are information regarding that task. I want to have a column (K) that indicates the last time the A cell of the same row as been modified. All data is inputted using a userform, which means the formula in the K cells must be inputted automatically and refer to the A cell of the same row.

Everything works for the data input and the K cell value do change when the A cell value changes(I was using a timestamps with minutes when I tested), however for some reason the dates seems to be updated daily ; when I opened the workbook this morning, all dates were today's -_-

Here is what I've done so far ;

1) Create a function that generate the date if the reference change

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yyyy")
Else
Timestamp = ""
End If
End Function

2) Defined the value of the K Cell in the userform VBA ("État" is the name of the A column header)

.Cells(iRow, 11).Value = "=Timestamp([@État])"

Thank you for your time!

 

[Gokhan Aycan]

I believe it is because you are assigning the function like a formula and it gets recalculated each time you open the workbook. Maybe just use .Value = Timestamp() ?

 

[AlexSrois]

I believe it is because you are assigning the function like a formula and it gets recalculated each time you open the workbook. Maybe just use .Value = Timestamp() ?

I need to put a reference in my Timestamp function so it knows when to update ; The formula wouldn't work that way, and if it did it wouldn't update when the value of the A cell of the same row was changed. Maybe using that function isn't the best way to do it but it is still the only way I see.

 

[Gokhan Aycan]

You can use the worksheet_change event to catch the change time.

If Not (Intersect(Target, Reference) is Nothing) then
    ' Code Here
End If

 

[johnnyL]

@AlexSrois are you sure you want to handle it like that? If Column A value is changed from something to "" you want the Column K to = "" ?

 

[AlexSrois]

@AlexSrois are you sure you want to handle it like that? If Column A value is changed from something to "" you want the Column K to = "" ?

I might be explaining something wrong, and probably misunderstanding a ton too

Column A is a dropdown list of status. If that status is changed, I want the date of the change to be displayed in the K column, on the same row. That way, for each row, we can see when the status is changed.

Here is my understanding of what I'm doing so far ;

If Reference.Value <> "" Then
' If Reference.Value is different from ""
Timestamp = Format(Now, "dd-mm-yyyy")
' Display today's date

.Cells(iRow, 11).Value = "=Timestamp([@État])"
'Set the 11th row value to =  Timestamp formula with ''Etat" (A Column) as the reference value

Like I said, this works perfectly, except from the fact that when the workbook is opened, the timestamp seems to trigger.

 

[johnnyL]

Ok make a Test copy of that workbook that you can play with.

Then instead of using the any of the code from your Post #1 here, try the following:

1) Right click on the sheet tab at the bottom of the sheet that you want to monitor the change in the Column A, select view code.
2) copy the following code into the window that pops up:

Sub Worksheet_Change(ByVal Target As Range)
'
    Dim RowThatChanged  As Long
'
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        RowThatChanged = Target.Row                                                                     ' Save the row value that was changed
        Range("K" & RowThatChanged).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub

3) Save that test workbook, close it then reopen it and see what happens when the Column a values are changed. The K column should reflect the date.
4) save the test workbook again and reopen it on tomorrow's date and see if the Column K values still retain the date from today.

 

[johnnyL]

Here is a shorter version of the code that should work instead of the the code in my previous post:

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        Range("K" & Target.Row).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub

 

[AlexSrois]

You can use the worksheet_change event to catch the change time.

If Not (Intersect(Target, Reference) is Nothing) then
    ' Code Here
End If

With johnnyL explanation I understand better what you meant and you seemed on the right path! Thank you for your time!

 

[AlexSrois]

Here is a shorter version of the code that should work instead of the the code in my previous post:

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column A changed
        Range("K" & Target.Row).Value = Format(Now, "dd-mm-yyyy")                                   ' Update Column K cell value
    End If
End Sub

That works perfectly!
I didn't about this ''Intersect'' thing ; will spend some time learning this. Thank you so much!