Remove old users to speed up shared workbook

Hi All,

My team use a shared, macro-enabled (.xlsm) workbook, with roughly 25 users in it at the same time, 24 hours a day.

Over a few days, the shared user list gets full up, sometimes people's entries appear in there 4 or 5 times each. This causes the workbook to get bogged down which eventually results in it becoming corrupted and most of the time data loss occurrs as a system backup has to be restored through 'previous versions' in the folder properties.

My only way to fix this at the moment is to regularly ask everyone to save their work and exit the spreadhseet, so I can unshare and re-share it, which clears the list. The issue with this is when multiple users try to save at the same time, the 'file locked' message appears, so each of the 25 odd users has to keep trying to save until they've all saved which ends up being hours sometimes that the spreadsheet is out of use. Then each has to confirm via email they've saved and exited (as I have to temporarily move the spreadsheet from the shared drive to my desktop to stop it being accessed whilst I'm trying to fix it).

With users all over the globe, working at all different times, this is just a complete pain in the posterior, as you can probably imagine. Also, despite being told not to numerous times, users leave the shared excel open and then log off, disconnecting the VPN, when they've finished their shift. I'm pretty sure this doesn't help the situation.

So I did a little bit of digging, and came accross the following VBA code (from superuser.com/questions/961918/how-do-you-prevent-corruption-of-shared-excel-files) which is supposed to remove all shared users who have been in the workbook for more than X hours.

I'm using all of the below macros but can't get the bolded "SharedUserCheck" macro to work.

I click run, I get a spinning wheel and then nothing happens (entires greater than 12 hours are still there). I'm trying to run this with the workbook still shared, as this is supposed to work:

Please Login or Register  to view this content.

I've got very minimal knowledge of VBA code, so wondering if someone could provide a simple explanation, for a noob?

I'm using Microsoft Office Standard 2016.

Kind Regards
Chris

Sounds like it's time to move to 365 and the nes 'Collaberation' functionality.

Many of us avoided shared workbooks like the plague since they just produce too many problems and seriously limit Excel's functionality. I fear that adding macros probably just compounds any difficulties.

Incidenatally please note we have a rule about wrapping macro code in [CODE ][ / CODE] tags

Hi All,

Wondering if anyone with knowledge of VBA could kindly provide a solution to the SharedUserCheck macro issue?

Regards
Chris