Fetch UserName & Date | Time

[MohitMathur1234]

Hi All,

I need your help in User Name and date & time.

I have some data for audit Where I want to check username & date (Who did audit) in last column. If another user do some changes in A to E then username should change.

AUDIT A1 B1 C1 D1 E1 USER DATE&TIME
AUDIT Yes No Yes No Yes mmohit 24-july 11:42am

Thanks

 

[Asheesh]

on what basis should it change the username...should it be the system username..??

 

[Ajesh]

You will need to adjust relevant range as per your need:

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("A1:E1")
If Not Intersect(Target, Rng) Is Nothing Then
Application.EnableEvents = False
Range("F1").Value = Environ("UserName")
Range("G1").Value = Now()
Application.EnableEvents = True
End If
End Sub

Thanks/Ajesh

 

[MohitMathur1234]

This is only for one row but i have lot of data for audit. please change the code according for lot of data.

 

[Ajesh]

What is the range of you data?

 

[MohitMathur1234]

It increase daily, means team do transaction on daily basis.

 

[Ajesh]

try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LstRow As Long
Dim Rng As Range
With ActiveSheet
LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Set Rng = Range("A1:E" & LstRow)
If Not Intersect(Target, Rng) Is Nothing Then
Application.EnableEvents = False
Range("F" & Target.Row).Value = Environ("UserName")
Range("G" & Target.Row).Value = Now()
Application.EnableEvents = True
End If
End Sub

Thanks/Ajesh

 

[MohitMathur1234]

Solved.!!

Thanks for quick response.. You saved my tool.

 

[Ajesh]

Glad to help.

Cheers