Close file when inactive for 10 minutes.

[Alberta Rose]

At times someone in our office has a certain file open to which others
need access. If the person hasn't made changes in 10 minutes, I'd like the
file to save and close automatically after this time period. I've tried a
couple of suggestions on this site, but not working. Any help?

 

[ryguy7272]

Use a TimeOut macro; sample here (watch for word wrap):
http://www.pcreview.co.uk/forums/thread-1749785.php

HTH,
Ryan---

 

[Alberta Rose]

Could you please tell me the difference between a standard module and a
workbook module?

 

[EricG]

Go into the Visual Basic Editor (Tools/Macro/Visual Basic Editor). If you
don't see the Project window on the left, select View/Project Explorer.
You'll see your Excel file listed there. There is a folder called "Microsoft
Excel Objects". If you open that folder, you'll see things like "Sheet1
(Sheet1)" and "ThisWorkbook".

Double-click on "ThisWorkbook", and the Visual Basic Editor opens the code
window for the workbook. Paste the workbook code in there.

If therre isn't already a standard module in the "Modules" folder, select
Insert/Module from the menu. Double click on the new module in the "Modules"
folder, and add the module code there.

HTH,

Eric

 

[Alberta Rose]

Thanks, this worked like a charm

Laurie

Go into the Visual Basic Editor (Tools/Macro/Visual Basic Editor). If you
don't see the Project window on the left, select View/Project Explorer.
You'll see your Excel file listed there. There is a folder called "Microsoft
Excel Objects". If you open that folder, you'll see things like "Sheet1
(Sheet1)" and "ThisWorkbook".

Double-click on "ThisWorkbook", and the Visual Basic Editor opens the code
window for the workbook. Paste the workbook code in there.

If therre isn't already a standard module in the "Modules" folder, select
Insert/Module from the menu. Double click on the new module in the "Modules"
folder, and add the module code there.

HTH,

Eric

 

[Dave Peterson]

If I were a user of this workbook, I'd be worried--even if I'm not the offender.

If you save that workbook that hasn't been touched in 10 minutes, how do you
know what you're saving?

If that user did something very bad (destroying lots of data or deleting lots of
sheets--either by mistake or on purpose (wanting to save it as a new name)),
then doesn't saving it just make matters worse?

And if you think you can close without saving, I'd hate to be the user who made
an hours worth of intricate changes only to lose them because I got a phone
call.

Heck, I'd hate to be the developer of that ontime procedure when the user
complains that he or she didn't do the damage--the developer did.

Personally, I think that this is a training issue. You have to get the users on
board to make changes and get out.

Even better would be to use a different application--one that supports multiple
concurrent users (Access or any real database program????).

That said, I used to have the same problem. Instead of closing the file
automatically, I'd write a record to a text file on a server that was open to
everyone (but no one knew about!) whenever any opened the workbook or closed it.

Then I'd just open that text file (readonly mode!) to see the last person to
open without closing.

 

[EricG]

Something intermediate that I have done many times is to put a very visible,
impossible to miss warning form that says something like "It's been a long
time since you did anything! Are you still working in the file
'Share_File.xls'? If not, please exit so others can use it." I have two
buttons: "Continue" and "Quit". The user makes the choice.

Also, the thought of making it a shared workbook just crossed my mind...

 

[Alberta Rose]

Excellent points Dave and Eric! I hadn't thought about the other variables
that could happen, eek... I like the idea of putting the warning on.

I'm also the Microsoft Access person for our company, and would love to get
this switched over to Access, but ... higher powers love Excel and are slowly
moving towards Access.

Do you have the coding I would put to give the Continue or Quit options?
Thanks for your help with this, much appreciated...

Laurie

 

[Alberta Rose]

Hi Dave. Could you explain to me how to record this to a text file?

 

[Dave Peterson]

The bad news is that the user would still have to be at their desk to hit one of
the buttons (and be observant, too <vbg>).

And if they've seen the reminder, they could close and save or discard their
changes.

But if I were going to ask, I'd create a small userform with 3 buttons:

Continue working
Close and discard changes
Save changes and close

The builtin message boxes don't offer customized buttons. So you'd probably
have to explain the difference between
Cancel
Yes
No
(or whatever 3 buttons you chose)

You'd still your existing ontime stuff, though--to get the prompt.

Dim myPrompt As String
Dim Resp As Long

myPrompt = "Click Cancel to continue working" _
& vbLf & "Click Yes to save and close" _
& vbLf & "Click No to close and discard"

Resp = MsgBox(prompt:=myPrompt, Buttons:=vbYesNoCancel)

Select Case Resp
Case Is = vbYes
ThisWorkbook.Close savechanges:=True
Case Is = vbNo
ThisWorkbook.Close savechanges:=False
Case Else 'cancel
'do nothing
End Select

=====
I've never found this a good solution. People walk away from their desks, get
phone calls, ... and just never notice the time in the workbook.

I know that I started locking my pc (with a password) when I walked away--just
to stop anyone from touching the stuff I was working on. I would imagine that
this could happen in your office, too.

 

[EricG]

Ironically, I have a good solution that I use for Access databases, including
a custom form with a timer that kicks a user out after they are idle for a
while, but saves their changes and provides the option to re-do those changes
when the user comes back. However, that solution is a little too complex for
this situation.

Dave, I understand your concerns, but if the workbook is not shared, and
some user has it opened and decides to take an extra long lunch, or even a
vacation, then everyone else who requires access to the workbook is impacted.
It is unacceptable one inconsiderate user to impact a whole organization.

What about the suggestion to make the workbook shared? When you do that,
multiple users can open and edit the file at the same time. Also, you have
access to a change history log so you can see who has been editing the file
over time. Finally, when you share a workbook, the users are prevented from
making structural changes to the workbook. They can't do things like
add/delete sheets or make formatting changes. The only way to make that kind
of change is to "unshare" the workbook, make the changes, and then share it
out again.

Tools/Share Workbook...

Check the "Allow changes by more than one user at the same time." box.

HTH,

Eric

 

[Dave Peterson]

That's why I said it was a training issue to start.

I still don't know how anyone else can make the a reasonable decision to save
changes or discard changes.

If your boss locked up the workbook and you saved (and destroyed the workbook)
that he had no intention of saving--or you discarded 2 hours of his work, would
you be in trouble?

Why not show the same deference to other employees--no matter how inconsiderate
they may be?

Dave, I understand your concerns, but if the workbook is not shared, and
some user has it opened and decides to take an extra long lunch, or even a
vacation, then everyone else who requires access to the workbook is impacted.
It is unacceptable one inconsiderate user to impact a whole organization.

<<snipped>>

 

[Dave Peterson]

Maybe something like:

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()
If LogTheData(myStr:="Open ") = False Then
MsgBox "Error with open logging--call me @ ####"
End If
End Sub
Sub Auto_Close()
If LogTheData(myStr:="Close") = False Then
MsgBox "Error with open logging--call me @ ####"
End If
End Sub
Function LogTheData(myStr As String) As Boolean

Dim myLogName As String
Dim FileNum As Long

myLogName = ThisWorkbook.FullName & "_Log.txt"

On Error GoTo ErrHandler:

FileNum = FreeFile
Close FileNum 'just in case

Open myLogName For Append As #FileNum

Print #FileNum, myStr _
& vbTab & ThisWorkbook.FullName _
& vbTab & Format(Now, "mm/dd/yyyy--hh:mm:ss") _
& vbTab & Application.UserName _
& vbTab & fOSUserName

Close #FileNum

LogTheData = True 'ok
Exit Function

ErrHandler:
Err.Clear
LogTheData = False 'faile
End Function

=====
The log file has to be on a server that everyone can write to. You may want to
change the location of the file to something that only you know about, but
everyone can write.

And you could add as much info as you want to that print command. I like it to
be a single line so that I can import it to excel.

Every so often, I can clean up the file--just don't lock it up so the logging
fails <vbg>.

Ps. I think I would move the log file to a common folder. Then I could look at
this log to see if there is anyone who is running their own copy of the
workbook. (That was a big problem where I worked with one of our pricing
workbooks.)

The official workbook would be updated, but people would be using their own
copy. So I'd look at the log (not based on the workbook's fullname) and ask
them to destroy their copies and explain why.

The second time, I'd visit their boss and explain to them that so-and-so was
using an out of date workbook and may be providing the customers with out of
date prices (and short changing us).


myLogName = ThisWorkbook.FullName & "_Log.txt"
would become:
myLogName = "\\server\sharename\Pricingworkbook" & "_Log.txt"