Date TIme & Username Timestamp

[jei eusebio]

Hi Ninjas,

Working on an excel file which saves the names of agents who were absent for the day, is it possible using VBA to automatically put a Timestamp with the Username on Column F everytime a comment is typed in column E or if the status is changed in column D?

Thanks in advance!

Jei

 

[Luke M]

Hi Jei,

Right-click on the sheet tab in question, view code, paste this in. Modify the msg if needed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim changeRng As Range
Dim uName As String
Dim curTime As Date
Dim newMsg As String

'Check if a cell of interest changed
Set changeRng = Intersect(Target, Range("D:E"))
If changeRng Is Nothing Then Exit Sub

'Since we're about to make our own changes, turn off events
Application.EnableEvents = False
'In case something goes wrong, make sure we turn things back on
On Error GoTo SafetyNet

'Log our info
uName = Application.UserName
curTime = Now
'What do you want timestamp to say?
newMsg = uName & " made a change on " & Format(curTime, "yyyy.mm.dd hh:mm:ss")

'Add the timestamp(s)
For Each c In changeRng.Cells
    Cells(c.Row, "F").Value = newMsg
Next c

SafetyNet:
'Make sure we turn this back on
Application.EnableEvents = True
End Sub

 

[jei eusebio]

Hi Luke,

Thanks a lot! Just what I wanted!