Macro to see who has accessed my spreadsheet and when

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
129
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
129
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows
Ok, maybe this then, placed in the ThisWorkbook module

Code:
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

Board Regular
Joined
Sep 4, 2012
Messages
129

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows
The info would go on Sheet2, but you can put it anywhere you like by changing the sheet reference in the code here...

Code:
With Sheets("Sheet2")
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows
Oops, sorry...it should have been Sheet2 NOT sheet1

Code:
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Instead of:
.Range("B" & LR + 1).Value = Environ("UserName")
I prefer:
.Range("B" & LR + 1).Value = Application.UserName
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,446
Of course, nothing will be captured if the user:
• closes the workbook without saving;
• deletes the record; or
• disables macros when opening the workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,069
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top