In this video, I demonstrate a way to work around the issue of Windows that allows more than one user to edit a file at the same time with Excel VBA.

Case Scenario: The members of a small team have access to the same Excel file on a server. Due to the nature of the work done on that file and the procedures involved (including filtering and macros,) the file cannot be edited by more than one user at a time. Windows has a system that is meant to advise the users who want to open a file when another user is already editing it. However, that system not always works and ends up allowing two different users editing the same file at the same time, which can be catastrophic because only the work of one of the users can be saved in that copy of the file.

Solution: One way of working around this issue is to create a system with VBA and implement it on the Open and BeforeClose events of the file that we want to make sure is never edited by more than one user at a time.

The VBA code used as demonstrated on the video is available below. To copy the code hover your mouse over the top right-hand corner of the code window and click “Copy to Clipboard.”

This solution was inspired by a blog post by Jon Peltier and used a portion of the code provided by Chis Newman on his blog.

Copy to Clipboard

Tip from Jon Peltier:

If somehow one workbook doesn’t delete the InUse_YES.txt file when it is done (VBA crash or other reason), it will lock out everyone until somebody manually removes the file. One way to help with this is to append a timestamp to the filename (InUse_2021-01-19_1400.txt) and allow anyone to access the file after certain duration has passed after the timestamp, though that might also lead to trouble if the duration isn’t long enough.

Learn Excel and connect with a community of Excel professionals and enthusiasts: attend for FREE the amazing sessions organized by the MS Excel Toronto Meetup Group – https://www.meetup.com/MSExcelToronto/