Date-time stamp

[Daniel]

Hello,

My name is Daniel,
Can you please help me with some code to do the following in Excel:

Have one spreadsheet (with some protection I think); whenever someone
alters the content of some cells, a DateTime stamp should be stored in another
cell (protected).

Thank you

 

[Jacob Skaria]

For any changes done to unprotected area Range("A1:A20") a date/time stamp is
set in Column C which is the protected area. Right click the sheet tab>View
Code> and paste the below code to the code module..

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then
Me.Unprotect Password:="dist"
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
Me.Protect Password:="dist"
End If
End If
Application.EnableEvents = True
End Sub


If this post helps click Yes

 

[Mike H]

Daniel,

This assumes the cells where you enter the data are not protected and in
this case it's A1 - A100. Right click your sheet tab, view code and paste the
code below in. Enter data entered in a1 - A100 and you get the timestamp in
the corresponding B1 - B100

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(, 1) = Now
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub

Mike

 

[Daniel]

Jacob, Mike : Thank you very much for your kind help

Daniel