Possible to track who accesses my spreadsheet?

[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

 

[Arvi Laanemets]

Hi

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

 

[Bucky]

That sounds perfect. How would I go about creating an open event

 

[Arvi Laanemets]

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!

 

[Bucky]

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

 

[Arvi Laanemets]

Hi

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

Arvi Laanemets

 

[Arvi Laanemets]

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

Hi

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

Arvi Laanemets

 

[Bucky]

Thank you Arvi, that worked perfect

 

[gilbert]

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

 

[Arvi Laanemets]

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.