How to prevent two users from editing the same file at the same time with Excel VBA

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.