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

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

in the workbookmodule
Code:
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
Code:
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

Board Regular
Joined
Jul 21, 2004
Messages
93
thanks - when my workload drops off again I'll have a play around.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top