How to track user activity using a macro?

[Rajesh Kr Joshi]

Hi,

Is it possible to track user activity using a macro. Who/when opened, what changes made and when it was saved etc.

Thanks
Rajesh

[Excel Fox]

Yes it is. Excel has various events at workbook level that will allow you to keep a track of the who/when etc, but for what changes were made, using VBA will be a bit cumbersome. Excel however had an inbuilt feature of tracking changes. Maybe you might want to explore that also.

[Rajesh Kr Joshi]

I have a tracker which is daily updated by user. Can you help with a code which can automaticly save log:
who Opend, date & Time, when saved, date & time.

Thanks in advance
Rajesh

[bakerman]

This should get you started.
On opening your file and not yet present a sheet will be created called Logfile as last sheet in your workbook.
Then UserName and Date/Time will be logged, the sheet protected and the workbook saved.
Just before closing your file Date/Time will be logged and the workbook saved again.

Dim lrow As Long

Private Sub Workbook_Open()
If Worksheets(Worksheets.Count).Name <> "Logfile" Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = "Logfile"
    With Sheets("Logfile")
        .Range("A1").Resize(, 3) = Split("UserName|Last opened|Last closed", "|")
        .Columns("A").ColumnWidth = 20
        .Columns("B:C").ColumnWidth = 18
        .Range("B:C").NumberFormat = "mm-dd-yyyy h:mm:ss"
    End With
End If
With Sheets("Logfile")
    .Protect Password:="Rajesh", UserInterfaceOnly:=True
    lrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lrow).Value = Environ("username")
    .Range("B" & lrow).Value = Now()
End With
ActiveWorkbook.Save
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets(Worksheets.Count).Name = "Logfile" Then
    Worksheets("Logfile").Range("C" & lrow).Value = Now()
End If
ActiveWorkbook.Save
End Sub

[Rajesh Kr Joshi]

Hi Thanks a lot...This worked great.

Thanks
Rajesh