MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Johan on January 08, 2002 5:47 PM

I need help on how to do the following plz: I have a field with information in it, if I were to alter that info I want for example that the field beside it to be updated with the system time, without entering the time in manually. Hence showing a time that field was updated. Thx in advance.

Posted by Tom Urtis on January 08, 2002 9:32 PM

Here's one way to do it

Let's say your field of interest is column A, so then the updates (per your request to be in the field beside it) would be in column B.

Right click on your sheet tab, left click on View Code, and paste this into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Range("B65536").End(xlUp).Offset(1, 0).Value = Format(Now(), "mm/dd/yy" & " " & "hh:mm:ss")
End Sub

Even though you said you want the update to be in the form of time, I took the liberty of also including the date as well. So, format column B as you prefer.

Hope this helps.

Tom Urtis