VBA Macro to check if worksheet is already opened by other user

[richi5000]

Hi,

in an Worksheet that is stored on a sharepoint I've implemented the following macro:

Private Sub Workbook_Open()
If Application.Version >= "16" Then
    ActiveWorkbook.LockServerFile
Else 'Under Excel 2016 version 
End If
End Sub

Reason for this: In Excel 2016 this file is opened as read-only. Thats why this macros checks for the version of Excel - and if its 16 or higher it enables editing automatically.


Problem: If an other user has the file already open, this produces an error.


Is there a macro command that I can include to check if the document is already opened by other user - so if that is true, it stops the loop and opens the file in read-only?

If not - how can I stop the macro in case of an error? (Result would be the same - it opens in read-only)!?
Thanks
Richie

 

[louisH]

Hi.

To catch error you can use:

On Error Resume Next
ActiveWorkbook.LockServerFile
If Err.Number <> 0 Then Exit Sub
Err.Clear
On Error Goto 0

 

[richi5000]

Thanks lousH,

that was really a quick reply Works perfectly fine

Hi.

To catch error you can use:

On Error Resume Next
ActiveWorkbook.LockServerFile
If Err.Number <> 0 Then Exit Sub
Err.Clear
On Error Goto 0