Is Excel macro to track activity possible

Asked By 20 points N/A Posted on -
qa-featured

Is Excel macro to track activity possible, Activities like who and when the file opened, what changes made and when it got saved.

SHARE
Answered By 0 points N/A #190472

Is Excel macro to track activity possible

qa-featured

Wassup Joseph, I think I will be able to help you with your query, indeed it is possible to track activity with the excel macro. What you need to do is to have a code on your tracker.

On opening your file and when not present, a sheet will be created known as log file as last seen in the workbook.

Code;

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

 

 

The UserName and Date/Time is logged, the sheet is protected and the workbook saved.
Just before closing your file 'Date/Time' will be logged and the workbook saved again.

I hope this solves your query

Thanks

Related Questions