From 1 specific excel file, I have a button which opens another excel file, however I only want it to open the other excel file if it is not already opened by someone else.
The below code works really well, except that Excel displays a "File In Use" message which offers the user 3 options: "Readonly","Notify","Cancel".
This message is displayed immediately after the Workbooks.Open command.
I don't want the user to see that message, because my vba will make the decision for them.
But how can I get excel to not show that message?
Sub Check_if_open()
Dim LogFile As Workbook
Dim OpenAttempt As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ReadOnlyReOpen:
Workbooks.Open Filename:="C:\Folder Location\File to open.xlsx"
Windows("File to open.xlsx").Activate
Set LogFile = ActiveWorkbook
If LogFile.ReadOnly Then
LogFile.Close
Debug.Print OpenAttempt
If OpenAttempt <= 5 Then
OpenAttempt = OpenAttempt + 1
GoTo ReadOnlyReOpen
Else
MsgBox "Log File is opened by someone else. Please try again in a bit."
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
End If
End If
MsgBox "Log File is open."
End Sub
Bookmarks