Macro to see who has accessed my spreadsheet and when

[DPChristman]

I know I have asked this before, but I can't find it.

I have a spreadsheet that I have about 7 people regularly accessing.

As is always the case, somebody messes the file up, or leaves it open, so others can't update.

What i am looking for is a macro that will recorded every time a user accesses the file, when, and preferably for how long.

The spreadsheet is already kind of big, pulling data from multiple sources, so I don't want to add more bulk to the size.

If possible, it would be nice to have this macro save historical records, and maybe in another file.

I am currently using Excel 2010. I know some options won't work in 2010

 

[Michael M]

Have a look at this thread....

https://www.mrexcel.com/forum/excel-questions/500810-vba-code-track-changes-workbook.html

 

[DPChristman]

Thanks, but really not what I am looking for.
The example in the thread you referenced is talking about capturing changes to sell contents.
I am just looking for who went in, and when.

 

[Michael M]

Ok, maybe this then, placed in the ThisWorkbook module

Private Sub Workbook_Open()
Dim LR As Long
    With Sheets("Sheet2")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & LR + 1).Value = Time
        .Range("B" & LR + 1).Value = Environ("UserName")
    End With
End Sub

 

[DPChristman]

Thanks. I will give that a shot, and let you know.

Where does the info go? Does it create another tab?

Ok, maybe this then, placed in the ThisWorkbook module

Private Sub Workbook_Open()
Dim LR As Long
    With Sheets("Sheet2")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & LR + 1).Value = Time
        .Range("B" & LR + 1).Value = Environ("UserName")
    End With
End Sub

 

[Michael M]

The info would go on Sheet2, but you can put it anywhere you like by changing the sheet reference in the code here...

With Sheets("Sheet2")

 

[Michael M]

Also made the sheet hidden
AND the workbook will save as well

Private Sub Workbook_Open()
Dim LR As Long
    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Visible = False
        .Range("A" & LR + 1).Value = Time
        .Range("B" & LR + 1).Value = Environ("UserName")
    End With
ActiveWorkbook.Save
End Sub

 

[Michael M]

Oops, sorry...it should have been Sheet2 NOT sheet1

Private Sub Workbook_Open()
Dim LR As Long
    With Sheets("Sheet2")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Visible = False
        .Range("A" & LR + 1).Value = Time
        .Range("B" & LR + 1).Value = Environ("UserName")
    End With
ActiveWorkbook.Save
End Sub

 

[jim may]

Instead of:
.Range("B" & LR + 1).Value = Environ("UserName")
I prefer:
.Range("B" & LR + 1).Value = Application.UserName

 

[Macropod]

Of course, nothing will be captured if the user:
• closes the workbook without saving;
• deletes the record; or
• disables macros when opening the workbook.