"File In Use" Message - how can this be disabled?

Thank you, I have now added the # around the code - will people be able to see my post?

Hi seaside,

I have used the following on shared files.

Private Sub Workbook_Open()
        
If ActiveWorkbook.ReadOnly Then                                         'Prevents access to a network folder if already open
        MsgBox "File is Read-only and will now close"
        ThisWorkbook.Close Saved = False
    Else
        MsgBox "OK to use this file"
    End If

End Sub

Perhaps you could add it to your code.

Regards

peterrc

Hi peterrc,
I tried putting the workbook_open sub into the source file and I still got the "File In Use" notification when I tried opening it from another excel workbook.

See here

http://www.ozgrid.com/forum/showthre...287#post371287

Hi everyone,
Thank you for your tips. I have tried them all but I'm still getting the "File In Use" notification.
However, I have found the cause of the problem - but I'm still after any advice if you have some?

In my excel workbook I have got an ActiveX Control Button ("Forms.CommandButton.1") which calls my vba code through the below:

Private Sub SubmitAssessment_Click()
'Application.Run "SubmitAssessment.Submit_Assessment"
Application.Run "SubmitAssessment.tst"
End Sub

This is causing excel to display the "File In Use" notification.
If I add in a Form Control Button instead, this is assigned to the Macro directly "Submit_Assessment" and it doesn't display the "File In Use" notification.

My problem now is how do I assign a private sub macro to a Form Control Button?

Hi seaside,

I have just tried to access one of my network files that I know is already being used by another member of staff.
Yes, I was allowed to select "Read Only" but as soon as I did another massage pops up "File is read only and will now close" and the only option is to click "OK".
In your first post you stated:-

I only want to open an excel file if it is not already opened by someone else.

The code I sent you doesn't allow them to open it if it is already opened - problem solved ?

Regards

peterrc

How did you try?
Just an example

Sub test()
    Dim fn As String, wb As Workbook
    fn = Application.GetOpenFilename("ExcelWorkBook,*.xls")
    If fn = "False" Then Exit Sub
    If Not IsFileOpen(fn) Then
        Set wb = Workbooks.Open(fn)
    Else
        For Each wb In Workbooks
            If wb.FullName = fn Then
                Set wb = wb: Exit For
            End If
        Next
    End If
    If wb Is Nothing Then
        MsgBox fn & " is in use"
        Exit Sub
    End If
    MsgBox wb.FullName & " is open now"
End Sub

Function IsFileOpen(fName As String) As Boolean
    Dim ff As Integer, errNum As Integer
    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function