keep a log of who accesses a file and how many times

I have about 12 workbooks and for most people who access those workbooks they are opened read-only because that is the access level that those people have been given to that particular location on our server. I am trying to come up with a way that will keep a log of how many times a workbook is accessed, who accessed it, when they accessed it and what tabs in the workbook they viewed. All 12 workbooks are identical in terms of tabs, layout and formatting but the data is unique to each workbook. I'm not a VBA guru so go easy on me. The data that is logged should never be deleted and would only grow with time. Thanks

My Apologies I have been away from work/technology for the better part of a week.

@DFLAK, I have attached a sample workbook for reference. That is kind of what I was thinking as well but I like your use of the word "Data Repository" that is a good way to define it.
As for collisions they should be minimal if at all because most of these workbooks have only two people who have access and one of those is a the director and their assistant. The assistant will most likely be the one accessing them most of the time.

@mgs73, Thank you, I dumped in the code and it kept running but nothing happened. I am sure it is absolutely user error.

dflak, you were right! once I walked myself through the code again, I realized it was working. It was working so well I didn't even realize it was doing anything. mgs73 thank you! You're code is fantastic and certainly runs very efficiently. It is writing the text files and has already been working and capturing who has been accessing the file.

mgs73,

Is it possible to password protect the .txt file once the file is created or maybe within the code itself?

The code above works great if you are on a PC but if you are accessing the file from a MAC then the code breaks. upon opening the file the first pop-up window I get says "Additional permissions are required to access the following files:" and the options are "cancel" or "Select" and when I choose "Select" it opens up the location that the excel file lives in but I cannot choose any files as they are all grayed out so than I am forced to hit cancel and that is when I get this error... Run-time error '75': Path/File access error. When I click "debug" the line of code that it highlights is:

Open ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & "_Log.txt" For Append As #FNum

(see above for full code)

I don't know enough about MAC's or VBA to know if there is a solution for this problem or if there is a different code that I can place in a MAC dedicated excel workbook. Currently the excel file is accessed by both PC and MAC users but I could create a separate file for PC and MAC users if someone could help me figure out the code.

Also, as far as password protection goes, I would say the short answer is no.

If all you want to do is hide the file from the users you could write the log file to a different directory. They will of course require write access to the directory, so will be able to find the file if they know to look for it. Another option is to make the file hidden:

SetAttr FilePathName, vbHidden

The downside is it would hide the file from you too.

Another idea is to make it Read Only. If you do this, the VBA code would need to make it Read/Write before writing to it, i.e.

Public Sub LogInfo(LogMessage As String)
  Dim LogFile as String
  Dim FNum As Long
  
  LogFile = ThisWorkbook.Path & Application.PathSeparator & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & "_Log.txt"
  FNum = FreeFile
  SetAttr LogFile, VbNormal
  Open LogFile For Append As #FNum
  Print #FNum, Format(Now, "yyyymmdd_hhmmss") & " " & LogMessage
  Close #FNum
  SetAttr LogFile, VbReadOnly
End Sub

mgs73, You are brilliant! Thank you.

As for locking the file I figured that wouldn't be an option but I think I might try writing the file to a different directory as you suggested. Thanks again!