Possible to track who accesses my spreadsheet?

  • Thread starter Thread starter Bucky
  • Start date Start date
B

Bucky

I was wondering if there is a way to track what users access/view
certain spreadsheet of mine on a network.

Thanks for your help
 
Hi

P.e. you can create an Open event, where user name and system date/time are
stored into table on very hidden sheet.
 
Hi

With your workbook opened, activate VBA-editor (Alt+F11).
In VBA-Project window you see VBAProject(YourWorkbook) with sheets and
ThisWorkbook as objects of project. Richt-click on ThisWorkbook and select
ViewCode from dropdown menu.
In Code window, select Workbook from left dropdown - an empty Open event is
created.
Fill in the code for event (p.e. user name can you get through
Application.Username propertie, I think). Close the VBA-editor, close and
save the workbook. It's done!
 
Thanks again Arvi. Unfortunately, I know absolutely nothing abou
visual basic. I was hoping you could kindly provide me with the cod
used once the open event is created.

Thanks again
 
Hi

Create an worksheet and name it p.e. 'Log'
Into cell A1 on Log enter text 'DateTime'
Into Cell B1 on Log enter text 'User'
Activate VBA editor, select sheet Log in VBA-Project window, and in
Properties window set sheet's Visible property to xlSheetVeryHidden

Create an workbooks Open event like
Private Sub Workbook_Open()
Dim LastRow As Long
LastROw = Sheets("Log").Range("A65000").End(xlUp).Row
Sheets("Log").Cells(LastRow + 1, 1).Value = Now
Sheets("Log").Cells(LastRow + 1, 2).Value = Application.UserName
End Sub

Protect your VBA-Project with password (right-click on project in VBAProject
window, select Properties from dropdown menu, and then activate Protection
tab).

Close VBA editor and save your workbook.

To view logs, you have to open VBA-Project window (you are asked for
password) and set sheet's Log Visible propertie to xlSheetVisible at first.
Or you can create links to sheet Log (you can have a special
password-protected workbook for this, where the log is mirrored).

NB! All this works when workbook isn't for shared use. With shared use all
will be more complex - started with retrieving user's name, and up to how to
write the log down when workbook is opened as read-only. Maybe someone more
experienced with such tasks can help you further.


Arvi Laanemets


Arvi Laanemets said:
Hi

Maybe tomorrow then - it's almost a hour past midnight here.

Arvi Laanemets
Click to expand...
 
Dear Arvi,

I am sorry for interrupting you....but I still "catch no balls". Coul
you elaborate further as I have totally zero knowledge on programmin
but I am keen to pick up this knowledge.

In your explanation....do we create two workbooks? One with the nam
'log' to log down the users, whilst the other is the working workboo
(u called it Open Workbook)? Please correct me if I am wrong.

Where shall we enter the VBA codes then? In the Log or Open Workbook
When you refer VBA-Project, which workbook are you referring to?

Please help me by elaborating further to enhance my understanding....i
possible, maybe you or Bucky could share the workbook for my bette
understanding. You may email to (e-mail address removed)

Thank you

Rgds,
Gilber
 
Hi

There is one workbook (whatever you call it), and Log is a sheet in it,
where info about using the workbook is stored in.
'Open' is an event for workbook. The workbook's Open event is called every
time, the workbook is opened.
 
Back
Top