Identify if shared workbook is locked by another user before running macro

[JustJohn91]

Hi All,

Ive searched and searched and cant find an answer to my query. If it has been answered already please point me in the right direction.

I have a shared excel workbook where multiple staff could be in at the same time. Staff input and update data via a userform. To populate the excel sheet with the data from the userform, the workbook is saved (To update to latest version of data), the data is populated and the workbook is saved again. The entire process takes 2-3 seconds. However, if a staff member has another excel workbook open, this time can increase.

If User A presses the save button, and User B the presses the save button before User A's process has completed, the sheet is locked for editing and User B's data isnt entered and is lost.

Is there a way to identify that the sheet is locked for editing and to wait and check again in 5 seconds?

Thanks In Advance,
John

 

[MCLIFTO8]

This will ask the user to try again. It gives them a chance to call the offending co worker in question, have them shut down the workbook and wait for windows to do it's thing unlockign the file. Which will take more than 5 seconds.

Function FileLocked(ByVal strFileName As String) As Boolean
    Dim Response As VbMsgBoxResult
   
CheckFile:
    On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    FileLocked = CBool(Err.Number <> 0)
    On Error GoTo 0
    If FileLocked Then
'read / write file in use
        Response = MsgBox("File Is Open For Editing By Another User." & Chr(10) & _
        "Do You Want To Try Again?", 37, "File In Use")
        If Response = vbRetry Then GoTo CheckFile
    End If
End Function

If FileLocked(strDestWorkbookPath & "\" & strDestWorkbookName) Then
        End
End If

 

[Jaafar Tribak]

Also, if you want to know the user who has the workbook open\locked , check this out