Macro to see who has accessed my spreadsheet and when

[DPChristman]

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

 

[jim may]

Regarding your #1 closes the workbook without saving..

Michael M's #8 thread # above ends with:

ActiveWorkbook.Save
End Sub

Doesn't this code-line save the workbook - without offering the User (opener) a Choice to save, or not?

​

 

[Joe4]

disables macros when opening the workbook.

This is a real concern, and is an easy way for people to bypass this process you are setting up.

What I have seen people do is put in BeforeSave code that hides all the data and password protects it, so if someone opened the file with disabling the macros, they would not be able to see or do anything.
Then, have code in Workbook_Open to unhide and unprotect the data (so that only will run IF they enable macros).

 

[Macropod]

Regarding your #1 closes the workbook without saving..

Michael M's #8 thread # above ends with:

ActiveWorkbook.Save
End Sub

Doesn't this code-line save the workbook - without offering the User (opener) a Choice to save, or not?

​

That is true, but it still fails to address either of the other two workarounds. At the end of the day, the problem is that the OP is trying to use VBA in a workbook to deal with an issue that that should be addressed by the network access monitoring.

 

[DPChristman]

That is true, but it still fails to address either of the other two workarounds. At the end of the day, the problem is that the OP is trying to use VBA in a workbook to deal with an issue that that should be addressed by the network access monitoring.

I can always just hide the tab. I am not worried about someone bypass the info capture.

None of the people using this spreadsheet have the skill level to do that.

I tried the script and it worked.

My only concern is that the file is currently in xlsx format.

Again, not a lot of skill level here, changing to an xlsm may be enough to confuse them.

They all have the shortcut bookmarked on their desktops (I did it when they were in), and I don't want to get in situation of having the data start being kept in twp places, and not match

 

[Michael M]

While the comments of others are correct, the answer provided is a simple way of recording the acesses of other users.
Sometimes we overthink what other usres might do / not do.....AND we give a little too much credit to other users abilities.
I'm guessing, after 30 years in the public service, that almost every single one of the OP's users won't even know there is...
1. a hidden sheet
2. a macro run at open
3. the workbook has been saved.

The OP simply wants to see who has used the workbook, to find the one / or more usrs that keep screwing with his work.

 

[Joe4]

that almost every single one of the OP's users won't even know there is...
...
2. a macro run at open

That can actually work against you here. In my experience with most users, since the default behavior of Excel typically is to give them a warning, and they need to click Enable, most of the time they either don't see the warning or ignore it and do not click it at all. So your Macros would never run in that instance. And I would highly advise changing the settings to allow all VBA (huge security risk). We have used Trusted Locations and Digitally Signed Documents in the past to combat that. But even that needs to be set-up or accepted on their computers the first time around.

 

[Michael M]

So why not add a "warning" sheet that pops up as the default sheet, when macros aren't enabled....with the message"please close this workbook, and enable macros before reopening"

 

[Joe4]

So why not add a "warning" sheet that pops up as the default sheet, when macros aren't enabled....with the message"please close this workbook, and enable macros before reopening"

You give them too much credit! Most of them wouldn't have any idea what that means and would call me anyway. These are the same people who used to think you could convert an Excel file to a text file by browsing to it in Windows Explorer and changing the extension from "xls" to "txt" (many of them didn't have any secondary education) I typically set them up the first time and walked them through it, then they were good going forward.

Actually, I do not work with that group of people anymore, and I do not get to do much development work in Excel anymore since I switched jobs. A little sad, I miss it!

 

[Michael M]

I do not get to do much development work in Excel anymore since I switched jobs.

Me too....but mine was called retirement !!

I guess the obvious choice for the OP, as he only has 7 other users, is to do a small training session about enabling macros for this one workbook.....without telling what it's for !!!