automatically close a workbook after X time

[nancyo]

I would like to set up a macro in a workbook which automatically saves/closes the workbook after a specific period of time.

The trick is, the macro should only run if there has been no activity/changes to the workbook in that period of time.

If a workbook is accidently left open on a computer, this will close it automatically.

I have been searching for info on how to do this, and need help. I have seen some info on different commands, i.e. xl autoclose, but am not getting very far. Any advice is appreciated.

 

[Ivan F Moala]

On 2002-08-16 08:51, nancyo wrote:
I would like to set up a macro in a workbook which automatically saves/closes the workbook after a specific period of time.

The trick is, the macro should only run if there has been no activity/changes to the workbook in that period of time.

If a workbook is accidently left open on a computer, this will close it automatically.

I have been searching for info on how to do this, and need help. I have seen some info on different commands, i.e. xl autoclose, but am not getting very far. Any advice is appreciated.

Hi nacyo
You will have to use these key commands
1) Application.Ontime
2) Workbook.save
2) Application.Quit

in conjuction with an Event procedure to detect when the workbook is left idle.

I would suggest
<pre/>
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub
</pre>

So when a sheets selection is changed (ie. you are working on it) it will run your Ontime macro > Ontime Macro
will run for a specified time UNTIL the Event is triggered again....ie working on the sheet resets the Ontime, leaving it idle leaves the timer going. When your predefined Time is met it saves & shuts down the Application.


This should get you started....post for problems you encounter setting this up.

 

[nancyo]

Thanks Ivan!!! I will work on this over the weekend and early next week. I'll let you know what happens. Still need to figure out the timing part. Will do more searches.

 

[nancyo]

I have been trying some simple macros, and can get the file to save and close automatically after a specified time, but only if I try an event such as open(), etc.

If I put in SheetSelectionChange, I get the file saving everytime I make a change. I need the ontime to work if there is NO changes to the workbook. Need to find a way to reset the ontime, if possible.

I tried the deactivate event, still no luck.

Any suggestions?
This message was edited by nancyo on 2002-08-23 08:01

 

[nancyo]

Can anyone explain if there is such a function as "on idle", some way to detect if the worksheet is idle...nothing in the help nor reference books...

 

[Ivan F Moala]

Hi Nancyo
Did you lookup the help file for Ontime?
Here is the output;

OnTime Method Example

This example runs my_Procedure 15 seconds from now.

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
This example runs my_Procedure at 5 P.M.

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False


What you will have to do is store your START
Ontime variable so that you can reference the
STOPPING of the Ontime macro.

Have a look @ Chips site for OnTime

http://www.cpearson.com/excel.htm

If you get into trouble post back

 

[nancyo]

Hey Ivan, thanks for responding.

I have looked at this site. I guess I'm just not getting it. I am not a programmer, so...

I will look at it again, and re-read some of my search. I understand the help in excel fine, and have a procedure set up which does what I want if I tie it in to an event such as workbook open (), as I explained above. I just can't figure out the idle part of the problem...

I will get back to you soon.

 

[Ivan F Moala]

On 2002-08-23 13:11, nancyo wrote:
Hey Ivan, thanks for responding.

I have looked at this site. I guess I'm just not getting it. I am not a programmer, so...

I will look at it again, and re-read some of my search. I understand the help in excel fine, and have a procedure set up which does what I want if I tie it in to an event such as workbook open (), as I explained above. I just can't figure out the idle part of the problem...

I will get back to you soon.

Hi Nacyo
Good on you for trying.....
If you do get stuck and you don't mind..then
send workbook with explanation. No promises
But I'll show you where and how etc..

 

[nancyo]

Hey Ivan,

What I think I need to do is have a macro which starts the timer, and a second macro that cancels the first and restarts the timer, somehow.

I will look at monday of next week, and probably dream about this over the weekend.

This is needed as a general type of option available to many files, so the workbook is basically irrelevant.

Have a good weekend..I'll post back.

 

[awilson2]

Something Similar

Although this is not exactly what you are looking for I did something similar. This will autoclose the document after 5 minutes of being open.



Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' this is 300 seconds or 5 Minutes
Public Const cRunWhat = "The_Sub"

Sub Autpen()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub


Sub The_Sub()
'
ActiveWorkbook.Save
ThisWorkbook.Close savechanges:=True
'
Autpen

End Sub