prevent two users from editing the same file at the same time|VBA & Macros|Excel Forum|My Online Training Hub

Afternoon All,

I have found a VBA code that would prevent 2 or more using a excel file

When opening a file a InUse_Yes.txt that would drop in together with the excel file. 

I looked at this VBA file which may have some merit to its name but these is a run time error 52 Bad File Name or Number.

Option Explicit

Const FileControlFolder = "FileControlFolder = C:\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

Thanks

Steve