When Multiple users edit a file 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.

Copy to Clipboard

Option Explicit
'SOURCE: solveandexcel.ca

Const FileControlFolder As String = "Z:\Database\FilesInUse\"
Dim FileControlSubFolder As String

Private Sub Workbook_Open()
    If ThisWorkbook.ReadOnly = False Then
        
        FileControlSubFolder = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
        
        If Dir(FileControlFolder & FileControlSubFolder & "\InUse_YES.txt") <> "" Then
            MsgBox "It seems that the " & FileControlSubFolder & _
				" file is being used by another user. Please come back later.", _
				vbCritical, "More than one user accessing the file."
            ThisWorkbook.Close SaveChanges:=False
        Else
            Name FileControlFolder & FileControlSubFolder & _
			"\InUse_NO.txt" As FileControlFolder & FileControlSubFolder & "\InUse_YES.txt"
            TextFile_Create (FileControlFolder & FileControlSubFolder & "\" & Environ("UserName") & ".txt")
        End If
        
        FileControlSubFolder = vbNullString

End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ReadOnly = False Then
    FileControlSubFolder = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

If Dir(FileControlFolder & FileControlSubFolder & "\InUse_YES.txt") <> "" And _
        Dir(FileControlFolder & FileControlSubFolder & "\" & Environ("UserName") & ".txt") <> "" Then
        
        Name FileControlFolder & FileControlSubFolder & _
			"\InUse_YES.txt" As FileControlFolder & FileControlSubFolder & "\InUse_NO.txt"
        Kill FileControlFolder & FileControlSubFolder & "\" & Environ("UserName") & ".txt"

End If
    
    FileControlSubFolder = vbNullString

End If

End Sub

Sub TextFile_Create(FilePath As String)
'PURPOSE: Create A New Text File
'Adapted from: www.TheSpreadsheetGuru.com

Dim TextFile As Integer

'What is the file path and name for the new text file?
  'FilePath = "C:\Users\chris\Desktop\MyFile.txt"

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file
  Open FilePath For Output As TextFile
  
'Save & Close Text File
  Close TextFile

End Sub

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/

Learn Excel and connect with a community of Excel professionals and enthusiasts: