Show last date a cell was modified

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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)

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

Thank you for your time!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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() ?
 
Upvote 0
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.
 
Upvote 0
You can use the worksheet_change event to catch the change time.

VBA Code:
If Not (Intersect(Target, Reference) is Nothing) then
    ' Code Here
End If
 
Upvote 0
@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 = "" ?
 
Upvote 0
@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 ;

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

VBA Code:
.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.
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 0
Here is a shorter version of the code that should work instead of the the code in my previous post:

VBA Code:
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
 
Upvote 0
Solution
You can use the worksheet_change event to catch the change time.

VBA Code:
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!
 
Upvote 0
Here is a shorter version of the code that should work instead of the the code in my previous post:

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top