My Excel file is located on a shared network folder at work. There are tons of lines of VB code, but I will explain the premise and my issue. This Excel program contains data and graphs, and the programing mostly just guides users how to properly change the data as needed. The program allows multiple users to have the Excel file open at the same time (the Excel file is password protected, so it forces users to open a Read-Only copy). The user will make changes to the data and the program will track all the changes being made. Once the user is satisfied with their changes, they select a "Sync" button. The user does not see anything that happens during the Sync process, but what happens is the program will first change the Excel file name, which allows the program to open the Excel file in non-read-only mode while the user still has the Read-only copy open, then it will make all the same changes that the user made, then it will save/close the non-read-only file, and finally it changes the Excel file name back to the original name.

An intermittent issue has been occurring in which the Excel file gets "locked for editing", which is only resolved by making sure that everyone closes any read-only copy they may have open. So, the issue arises likely when someone performs a Sync, and either someone else either already has another read-only copy open, or someone opens a new copy before the "syncing" user closes. When the second (or third, or fourth, etc.) user now tries to Sync, it will not work because the Excel file is "locked for editing."

Again, this issue is intermittent, many times everything works fine, even with multiple Syncs when multiple users have it open. I believe something is happening with the non-read-only file being opened/closed during a Sync and maybe the hidden file (starts with "~$") that tracks who has the file open. Maybe this is partly due to the file being located on a network location? When the issue occurs, I've tried to delete the hidden file, but that doesn't resolve the issue. The only thing that resolves the issue is for everyone to close any copy they may have open (it has to be everyone - if the original "syncing" user closes but others still have it open, the file will continue to be locked). Once everyone is out, it seems to "reset" and the file is unlocked.

Anyone have any ideas on how to prevent the file from being locked? And yes, I do realized that having multiple users change data at the same time is a better task for an Access Database, but I wanted to have certain graph features, etc. and I was also more familiar with coding for Excel rather than Access.