Sunday, September 27, 2009

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

9 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I know this article is a few years old and you are using it to log only certain events Open/Save/Print.

    I have a similar script that I'm using to log every excel action, including individual cell changes via the Workbook_SheetChange event.

    The one thing I can't figure out is how to capture a copy/paste event.

    Do you have any insight into how to call a function after someone copies/pastes data?

    ReplyDelete
    Replies
    1. Mark can you please share the script that you are using to log every excel action, including individual cell changes via the Workbook_SheetChange event.

      Delete
  3. Mark, could you please share the script to view cell changes, please? Thanks!!

    ReplyDelete
  4. I think I'm looking for something similar to this article. I want to create a button on a review worksheet. The button when clicked by a user records their sign-off as a time/date log on another hidden worksheet. Am I searching in the correct direction?

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Im no expert, but I believe you just made an excellent point. You certainly fully understand what youre speaking about, and I can truly get behind that.
    excel vba courses london

    ReplyDelete