Read Only Workbook - How to Log who reads the workbook

I have a Ready Only workbook that is available to 100 people.  I want to determine who uses the workbook.  However, I can not add code to the OnOpen event as since the Workbook is read only then my code can not write the users name to a cell.  My other concern is a macro may open this read only workbook and I do not want to impact any macros that may open this read only workbook and extract info..

Any suggestions on how I can figure out who uses the workbook without having to email all 100 individuals..
upobDaPlayaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Place your workbook behind a CMS which forces a login for access.

You'll know instantly.

Likely much easier to use a working CMS, like WordPress, than hand roll your own.
0
upobDaPlayaAuthor Commented:
Whats a CMS...Whatever it is I think that solution would be problematic as if today am unaware that a macro opens this read only workbook and then out of nowhere I put up a CMS I assume the macro would fail ?
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
CMS == WordPress, Drupal, Joomla, 100s of others.

If you're new to CMS software, stick with WordPress.

With a CMS, you'd just setup your workbook as restricted content, so a person has to login to use your workbook. Then just track user stats.

You can also go a step further + break apart your workbook into separate content pages, to gather more granular data.

Read up on WordPress. You'll likely be surprised by all the features a CMS can provide for you.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roy CoxGroup Finance ManagerCommented:
Maybe write to a text file. Create a text file named log, change the path in the code to suit

Option Explicit

Private Sub Workbook_Open()
    Dim sPath As String
    sPath = ThisWorkbook.Path & Application.PathSeparator & "log.txt"    ''///Change to your folder
    Open sPath For Append As #1
    Write #1, Application.UserName & " , " & Format(Now, "yyyy/mm/dd hh:mm")
    Close #1
End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
This will be better for multi users

Option Explicit


Private Sub Workbook_Open()
    Dim iHandle As Integer
    Dim sPath As String

    iHandle = FreeFile

    sPath = ThisWorkbook.Path & Application.PathSeparator & "Log.txt"    ''///Change as to your folder
    Open sPath For Append As iHandle
    Write #iHandle, Application.UserName & " ," & Format(Now, "yyyy/mm/dd hh:mm")
    Close #iHandle

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
Hi Roy, what is the purpose of the ihandle...
0
Roy CoxGroup Finance ManagerCommented:
The VBA FreeFile function reserves the next available file number for VBA file input/output. When you open a file for writing or reading in VBA, you must refer to that file with a unique integer file number. The FreeFile function determines this unique file number for you so you can use it to open, read, and write files using VBA. It avoids a conflict if your chosen number has been used.
0
upobDaPlayaAuthor Commented:
Thanks for the detail
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.