Setting a timeout for Excel workbook

[newbalance]

Hello,
I am new to VBA, but i need to create a code for a workbook
that will do the following:
> once opened, a counter in the background will start at 10:00 -> 0:00
> meanwhile, user can update data in the spreadsheet
> once timeout is reached (after 10min), the workbook will automatically be saved and closed.

Windows OS is XP
Excel Ver is 2003 or 2010.

Appreciate any advise,
Thanks.

[GTO]

What if they are still updating?

The other risk (and bigger risk in my opinion), is if the user may have entered garbage and not corrected it at the moment we 'automatically saved and closed'. YIKES! You just forced the user to garbage-up the WB!

Mark

[newbalance]

good point GTO.
Perhaps, 20 seconds before timeout is reached a msgbox can pop saying: 'Finish your work, workbook is about to save & close'.

[Bob Phillips]

How about resetting the timer every time they make a change, and then save and close only after 10 minutes of no activity?

[newbalance]

Even a better choice.

[Bob Phillips]

In ThisWorkbook code module, add



Const Timeout As Long = 10 ' 10 minutes

Private Sub Workbook_Open()
dTime = Time
On Error Resume Next
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe"
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe", , False
dTime = Time
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe"
On Error GoTo 0
End Sub


and in a standard code module add this code


Public dTime As Double

Sub CloseMe()
ThisWorkbook.Close SaveChanges:=True
End Sub

[newbalance]

Thanks!
I have tested the code in a workbook, and it works perfectly.
However, I also must force users to enable mocros in their Excel software,
so I found this code here in this forum:
"Force users to enable macros in a workbook" by Ken Puls (I was unable to paste a direct link)
Tried to combine the two codes in my workbook...
Like I said, I'm new to VBA,
so at timeout I got a message to save the workbook (instead of doing so in the background and closing).
Need help to make two codes play nicely together...

[mancubus]

hi.

VBAx has a kb article for this as well. download and unzip the attached file here. the file contains all the procedures in the article.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

open Thisworkbook Code module.
put Const Timeout As Long = 10 at top of the module.
copy the below bit to existing Workbook_Open event, before the End Sub line.


dTime = Time
On Error Resume Next
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe"
On Error Goto 0

[Bob Phillips]

Show us the full code including the VBA force code.

[GTO]

Greetings macubus,

Not to interrupt, but the link shows johnske's suggestion for enforcing macro enabling. Utterly without malice, BeforeClose is too easily beatable. A pseudo 'AfterSave' and presumably (with 2010+) an actual AfterSave would be the way to go.

Mark

(I say presumably, as I have not yet needed the AfterSave event.)

[newbalance]

I was able to merge this recent 'force macros' code suggestion (by mancubus).
and made modification to both modules: ThisWorkBook and Module.
However, one issue I noticed so far: when a cell is being edited by user, timeout is disabled (no handling)
so workbook stays occupied.
I'm trying to attached my sample workbook with the codes.
indexOf11542