Excel Macros : Log User Activity to Log Sheet

We all store Excel workbooks on shared drives. While those files are accessed by many users but there is no track about who did what at various points of time. However this problem can be resolved with the help of VBA. A VBA programme with following capability can help us to get the log of user activity.

 
     1. Add sheet named Log in case it is does not exist in the file.
     2. Record user activity based on events to Log sheet.
     3. Along with recording a event, it records user information and time of event.
     4. Manage log size and removes old entries while creating space for new entries.
     5. User can view the log but can not make changes to the log.

Following code has all these capabilities. Macro given below records user activity based on event information passed to it by another macro. You will need to copy this code to regular VBA module of your workbook

Sub Elog(Evnt As String) '*************************************************************************************************** '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 27, 2009 * '*************************************************************************************************** Application.ScreenUpdating = False Dim cRecord As Long cSheet = ActiveSheet.Name If SheetExists("Log") = False Then Sheets.Add.Name = "Log" Sheets("Log").Select ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True End If Sheets("Log").Visible = True Sheets("Log").Select ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True cRecord = Range("A1") If cRecord <= 2 Then cRecord = 3 Range("A2").Value = "Event" Range("B2").Value = "User Name" Range("C2").Value = "Domain" Range("D2").Value = "Computer" Range("E2").Value = "Date and Time" End If If Len(Evnt) < 25 Then Evnt = Application.Rept(" ", 25 - Len(Evnt)) & Evnt Range("A" & cRecord).Value = Evnt Range("B" & cRecord).Value = Environ("UserName") Range("C" & cRecord).Value = Environ("USERDOMAIN") Range("D" & cRecord).Value = Environ("COMPUTERNAME") Range("E" & cRecord).Value = Now() cRecord = cRecord + 1 If cRecord > 20002 Then Range("A3:A5002").Select dRows = Selection.Rows.Count Selection.EntireRow.Delete cRecord = cRecord - dRows End If Range("A1") = cRecord Columns.AutoFit Sheets(cSheet).Select Sheets("Log").Visible = xlVeryHidden Application.ScreenUpdating = True End Sub Function SheetExists(SheetName As String) As Boolean On Error GoTo SheetDoesnotExit If Len(Sheets(SheetName).Name) > 0 Then SheetExists = True Exit Function End If SheetDoesnotExit: SheetExists = False End Function Sub ViewLog() Sheets("Log").Visible = True Sheets("Log").Select End Sub Sub HideLog() Sheets("Log").Visible = xlVeryHidden End Sub

Following macros record events like

"Open" , "Save" and "Print"

and pass on the information to above macro to record user activity.

You will need to copy this code to worksheet module of your workbook

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim Evnt As String Evnt = "Print" Call Elog(Evnt) End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Evnt As String Evnt = "Save" Call Elog(Evnt) End Sub Private Sub Workbook_Open() Dim Evnt As String Evnt = "Open" Call Elog(Evnt) End Sub

Since this code will record user activity only in case macros are enabled, do not forget to force users to enable macros while useing the particular file.

Download file having these macros to record user activity to a log sheet. Check how it works

record user activity , track user activity, logging user activity, log user activity , monitor user activity