Maybe you could find an easier way to see who to yell at--create a log that
shows when the file is opened or closed. Then just open that log file (in a
readonly mode--notepad worked ok for me), look, close and yell.
If you tie up that log file, it may cause a similar problem that you're trying
to solve <vbg>.
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Sub Auto_Open()
Dim FileNum As Long
If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If
FileNum = FreeFile
Close #FileNum
'use a filename like c:\excel\book1.xls.txt
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "OPEN: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum
End Sub
Sub Auto_close()
Dim FileNum As Long
If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If
FileNum = FreeFile
Close #FileNum
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "CLOSE: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum
End Sub
======
When I've done this kind of thing, I find that some users are using the wrong
workbook--they've saved a copy elsewhere. The log is a nice way to find out who
to yell at for that, too.
I've always just cleaned up the .txt file manually. But you could experiment a
bit.
Use "for output" in the auto_open procedure and "for append" in the auto_close
and you should only get a max of 2 records.
Use "for output" in both, and you'll only keep one record.
====
This assumes that users will always enable macros, too.
