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.
BaberA62Asked:
Who is Participating?
 
Rgonzo1971Commented:
YOu could use a macro to see the sheet

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

Open in new window

0
 
Rgonzo1971Commented:
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
0
 
BaberA62Author Commented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
BaberA62Author Commented:
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.
0
 
Rgonzo1971Commented:
Pls correct the point attribution to reflect your comment
0
 
BaberA62Author Commented:
Thanks RGonzo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.