A l'ouverture d'un classeur partagé, comment savoir qui est déjà en train de travailler dessus ? Comment lister dans un fichier log les utilisateurs successifs ?

Dans un module standard du classeur partagé :

Public Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Public Declare Function GetComputerName Lib "kernel32" Alias

 "GetComputerNameA" _
 (ByVal lpBuffer As String, nSize As Long) As Long

Function OSUserName() As String
Dim Buffer As String * 256
Dim BuffLen As Long
BuffLen = 256
If GetUserName(Buffer, BuffLen) Then _
OSUserName = Left(Buffer, BuffLen - 1)
End Function

Function OSMachineName() As String
Dim Buffer As String * 256
Dim BuffLen As Long
Dim lngX As Long
Dim strCompName As String
BuffLen = 255
If GetComputerName(Buffer, BuffLen) Then _
OSMachineName = Left(Buffer, BuffLen)
End Function

sub Startup()
 'Paul, mpep
 Dim ShowUsers
 Set ShowUsers = Application.CommandBars("Standard"). _
    Controls.Add(Type:=msoControlButton, ID:\=2040, Before:=13)
 
 ShowUsers.Execute
 Application.CommandBars("Standard").Controls(13).Delete
 
 end sub

'Dans le module ThisWorkbook du classeur partagé :

Tracer l'utilisation d'un classeur partagé dans un fichier log.txt 'une solution "complète" de Dave Peterson, mpep, qui suppose que 'tous les utilisateurs du classeur partagé disposent des droits d 'accès 'en lecture/écriture pour le dossier où se trouve le classeur partagé.

Private sub Workbook_Open()
 
  Dim FileNum As Long
 
  FileNum = FreeFile
  Open ThisWorkbook.Path & "\log.txt" For Append As #FileNum
 
  Print #FileNum, ThisWorkbook.FullName & " OPENED:\ " & _
    Format(Now, "yyyy-mm-dd hh:mm:ss") & " User: " & _
    OSUserName & " from computer: " & OSMachineName
  Close #FileNum
 end sub

Private sub Workbook_BeforeClose(Cancel As Boolean)
 
  Dim FileNum As Long
 
  FileNum = FreeFile
  Open ThisWorkbook.Path & "\log.txt" For Append As #FileNum
 
  Print #FileNum, ThisWorkbook.FullName & " CLOSED:\ " & _
    Format(Now, "yyyy-mm-dd hh:mm:ss") & " User: " & _
    OSUserName & " from computer: " & OSMachineName
  Close #FileNum
 
  Cancel = False
 
 end sub