asked on

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

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.

Rgonzo1971

Hi,

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

Regards

BaberA62

ASKER

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.

BaberA62

ASKER

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.

Rgonzo1971

Pls correct the point attribution to reflect your comment

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BaberA62

ASKER

Thanks RGonzo