Create a timer to promt to close a file after a time

[rogernz]

I want to create a macro that autostarts a timer when a file is opened, after a specified time period (say ten minutes) a userform pops up on screen (preferably on top of ALL open windows independant of program) asking the user if they are finished using the file, if not they can push a timer reset button, if so they can push a close now and save button. The file is required for access by numerous people across a small network. I have a limited understanding of VBA applications and userforms, however this is well beyond my current capabilities.

 

[GlennUK]

Have you looked at the OnTime method in the VBA help?

 

[erik.van.geit]

Hi,

this is some basic code
you will have to add more ...

in the workbookmodule

Option Explicit 

Private Sub Workbook_Open() 
sec = 3 
Run "do_something" 
End Sub 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Run "stop_it" 
End Sub

in a "normal" module

Option Explicit 

Public sec As Integer 
Public when As Variant 

Sub do_something() 
when = Now + sec / 60 / 60 / 24 
Application.OnTime when, "do_something" 
'samplecode writing to sheet: here you can close the file
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Time 
End Sub 

Private Sub stop_it() 
Application.OnTime EarliestTime:=when, Procedure:="do_something", schedule:=False 
End Sub

kind regards,
Erik

 

[rogernz]

thanks - when my workload drops off again I'll have a play around.

 

[erik.van.geit]

thanks - when my workload drops off again I'll have a play around.

when you don't see replies, it's because of my internet-connection which possibly will close within 2 hours till about 22th sept