Excel 2010: Default actions and message boxes depending on whether file is in use.

Sharing workbooks is always problematic and should be avoided. I think the reason the code does not run when you open the workbook as second user  is because as far Excel is concerned that event has already run.

This code will show who is using the workbook whilst shared.

Option Explicit

Private Sub Workbook_Open()
    Application.DisplayAlerts = False
    If ThisWorkbook.MultiUserEditing = False Then
        MsgBox "You may continue to add data, but please close when finished. Others need to use it.", vbInformation, "File not shared"
    Else
        MsgBox "This workbook is already in use. ", vbCritical, "Closing"
        Application.Dialogs(xlDialogFileSharing).Show
        ThisWorkbook.Close False
    End If
    Application.DisplayAlerts = True
End Sub

Select all Open in new window

Also, this code can be useful to close a workbook if a user leaves it open.First of all set a time limit eg 10 minutes .

Private Sub Workbook_Open() 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub 

Select all Open in new window

This resets the timer if a change in the workbook occurs

 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:10:00")
    RunTime
End Sub

Select all Open in new window

If no changes have occurred then this runs

Public EndTime 
Sub RunTime() 
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub 
 
Sub CloseWB() 
    Application.DisplayAlerts = False 
    With ThisWorkbook 
        .Saved = True 
        .Close 
    End With 
End Sub 

Select all Open in new window