Very good idea Dave
Our manufacturing users have generic logon names for a workstation so it
still leaves a question of whom to yell at. At least I would know which
workstation.
Thanks a bunch.

"Dave Peterson" wrote:

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.


Steve wrote:

Gotcha
No way around that I guess. We'll just have to locate the offending computer
Thanks

"Sandy Mann" wrote:

Steve,

By "edit mode" I mean just be in the middle of entering something into a
cell. So if you click into a cell and enter: Steve
but don't press Enter, you will find that you cannot then run a Macro.

So if someone leaves that sheet like that the closing Macro will not run.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson






--

Dave Peterson