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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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

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.
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.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Pls correct the point attribution to reflect your comment
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BaberA62Author Commented:
Thanks RGonzo
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.