Automatically update a cell with last modified date and time [Solved/Closed]

Report
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
-
Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020
-
I am maintaining a Test Environment Catalogue, with various users editing it. It is change controlled in SharePoint, however, I'd like to insert a cell labelled "Last Updated" - and the cell should reflect the last date and time the excel sheet was edited. (Only edited... not viewed... )

Columns B:K have content that could be edited
I have added a row with cell A1: Last Updated
And B1: <Cell that will contain dd/mm/yy hh:mm>

I have tried to create a module:

Public Function ModDate()

ModDate = Format(FileDateTime(ThisWorkbook.FullName), "dd/mm/yy hh:n ampm")

End Function

But this does not work

And I tried:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("C:k"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub


But could not quite understand how to modify this code for my sheet....

Any help from your end would be greatly appreciated....

Many Thanks,
Naz

1 reply

Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
432
Hi Naz,

Give the following code a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B2:K" & Rows.Count), Target) Is Nothing Then Exit Sub
Range("B1").Value = Now
End Sub


To change the date format, if necessary, go to the cell properties (Ctrl+1).

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3171 users have said thank you to us this month

Posts
10
Registration date
Monday March 25, 2019
Status
Member
Last seen
August 6, 2020

Works! Perfect solution. Thanks TrowaD - again!