We help IT Professionals succeed at work.

How do I log the users of a workbook on a spreadsheet?

BaberA62 asked
I would like to log the users of a workbook, on a spreadsheet within the workbook. I have a team of 12 users and would like to capture/record the following info username, time the workbook was opened, time the workbook was closed.

Is this possible? If so I really would appreciate some assistance. Thanks.
Watch Question

Top Expert 2016


pls try in thisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    LogChange ("Closed")
End Sub

Private Sub Workbook_Open()
    LogChange ("Opened")
End Sub

Open in new window

and in a normal module

Public Function LogChange(Optional Message)

Dim StartTime As Double
Dim TempArray() As Variant
Dim TheRange As Range
Application.ScreenUpdating = False

ReDim TempArray(0, 5)
If Evaluate("=ISREF(Log!A1)") = False Then
    Sheets.Add.Name = "Log"
    Sheets("Log").Visible = xlVeryHidden
End If

Lastrow = Sheets("Log").Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
FirstCell = "A" & Lastrow
LastCell = "F" & Lastrow
TempArray(0, 0) = FormatDateTime(Now, vbShortDate)
TempArray(0, 1) = FormatDateTime(Now, vbLongTime)
TempArray(0, 2) = Environ$("username")
TempArray(0, 3) = Environ$("computername")
TempArray(0, 4) = ActiveSheet.Name
TempArray(0, 5) = Message

Set TheRange = Sheets("Log").Range(FirstCell, LastCell)
TheRange.Value = TempArray
Application.ScreenUpdating = True

End Function

Open in new window



HI RGonzo,

How do I get to see the log sheet? I can see that it has been very hidden by the macro but I am unable to view it.


I've requested that this question be closed as follows:

Accepted answer: 0 points for BaberA62's comment #a40641172

for the following reason:

This solved the problem superbly, capturing all the users of the spreadsheet.
Top Expert 2016

Pls correct the point attribution to reflect your comment
Top Expert 2016
YOu could use a macro to see the sheet

Sub Macro
If Sheets("Log").Visible = xlSheetVeryHidden Then
    Sheets("Log").Visible = xlSheetVisible
   Sheets("Log").Visible = xlSheetVeryHidden
End If
End Sub

Open in new window


Thanks RGonzo