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

Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
- - Latest reply: NazCarr
Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
- May 16, 2019 at 08:01 AM
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
See more 

1 reply

Best answer
Posts
2565
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 18, 2019
370
1
Thank you
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

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6070 users have said thank you to us this month

NazCarr
Posts
9
Registration date
Monday March 25, 2019
Status
Member
Last seen
May 16, 2019
-
Works! Perfect solution. Thanks TrowaD - again!
Respond to TrowaD