Last Updated Worksheet Date/Time

[HWillis]

Hello,Hoping someone may be able to help please.I have 20 odd worksheets all relating to different companies.I need to know when the last update on individual worksheets was.I have used the below code - but this won't work as I have the worksheets protected so people can only add into specific cells. How can I enable the update but still keep the locked cells protected?Many thanks, H. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C5:E164")) Is Nothing Then Range("D3") = Format(Now, "mm/dd/yyyy h:mm") End IfEnd Sub

 

[jmacleary]

Hi there

Something like this should work (change password etc. as needed)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect
If Not Intersect(Target, Range("C5:E164")) Is Nothing Then
    Range("D3") = Format(Now, "mm/dd/yyyy h:mm")
End If
ActiveSheet.Protect Password:="bill", DrawingObjects:=True, Contents:=True, Scenarios:=True

Application.EnableEvents = True
End Sub

 

[HWillis]

Thank you - much appreciated - worked a treat.

Hi there

Something like this should work (change password etc. as needed)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect
If Not Intersect(Target, Range("C5:E164")) Is Nothing Then
    Range("D3") = Format(Now, "mm/dd/yyyy h:mm")
End If
ActiveSheet.Protect Password:="bill", DrawingObjects:=True, Contents:=True, Scenarios:=True

Application.EnableEvents = True
End Sub

 

[HWillis]

Hi there jmacleary..........
Unfortunately the code has stopped updating - I am not sure why?
I have the code in the relevant worksheet code.
Many thanks, H.

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


ActiveSheet.Unprotect


If Not Intersect(Target, Range("C5:E165")) Is Nothing Then
    Range("B3") = "Last updated - " & Format(Now, "dddd dd/mm/yyyy") & " at " & Format(Time, "h:mmam/pm") & " by " & Application.UserName
End If


ActiveSheet.Protect


Application.EnableEvents = True


End Sub