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

Report
Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
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
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
420
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 3939 users have said thank you to us this month

Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019

Works! Perfect solution. Thanks TrowaD - again!