Timed Sub

[Paleo]

Hi I would like to know how could I make a Sub called lets say Teste() to be executed after 30 minutes of excel inactivity.

Any ideas?

[gibbo1715]

im sure theres a better way but you could use the following code in the Worksheet_SelectionChange event if your only using one sheet, its worked for me in the past but didnt really like this option


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vartimer As Variant
' The next line sets the time in hours, minutes and seconds
vartimer = Format(Now + TimeSerial(0, 30, 0), "hh:mm:ss")
If vartimer = "" Then Exit Sub
' The action of the timer is to call a nother macro called test
Application.OnTime TimeValue(vartimer), "test"
End Sub

[Ken Puls]

Hi Carlos,

If you haven't visited Chip Pearson's site yet, you may want to give it a go. He has a scheduling sub here (http://www.cpearson.com/excel/ontime.htm)

Personally, I'd set that up, then maybe reset the timer constantly using this event in the "ThisWorkbook" module:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call StartTimer
End Sub

In this fashion, every time the user makes a change to any sheet, the timer should be reset. If they sit idle, it won't be and voila!

Having said that, I've never tried it myself!:giggle

[Paleo]

Hi guys,

thank you very much. I much appreciated this site Ken, thanks.

[Paleo]

Problem solved!

[Ken Puls]

I much appreciated this site Ken, thanks.
No problem. Chip's got a ton of awesome info in there about a variety of things.:yes


Problem solved!
So what was the answer? :*)

[Paleo]

Hi Ken sorry for not posting it here in the first place:

Here is it:

At This_Workbook


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call Timer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call Timer
End Sub


At Module1


Public vartimer As Variant

Sub Timer()
On Error Resume Next
Application.OnTime earliesttime:=vartimer, _
procedure:="Fecha", schedule:=False
On Error GoTo 0
vartimer = Format(Now + TimeSerial(0, 30, 0), "hh:mm:ss")
If vartimer = "" Then Exit Sub
Application.OnTime TimeValue(vartimer), "Fecha"
End Sub

[Ken Puls]

Hey Carlos,

Just a thought, but you might want to through in a Workbook_Deactivate or Worksheet_SheetDeactivate to turn the timer off as well. Maybe even a Workbook_BeforeClose.

I'm not sure about the latter, but if you drop your routines in, then move to another book, it will still trigger the macro.

Could be your intention though...:dunno

[Paleo]

Hi Ken,

yes, actually thats my intention. If its not active I am gonna close it using the Sub Fecha() that in portuguese means "close".

[Ken Puls]

Fair enough, but I'd still recommend turning the timer off in the "Workbook_BeforeClose" event.

At least make sure you test it with another WB open and see what happens. ;)

[Paleo]

Why should I put it at Workbook_BeforeClose? If its closed the timer will be killed anyway?

The second assumption is pretty right in my case because I have coded to close the application, not only the workbook, so if its leaved to work on another workbook, both are gonna be closed. But that was intentional. (Is it a ditactorial code or what?:thumb )

You know, sometimes I just feel I kinda hate the user and start punishing he/she
:devil: . (just kidding)

[Ken Puls]

Try this...

Open two workbooks. Copy all your code into workbook1, setting the time to 1 mintue instead of 30. Then go clicking around the sheet1 in Workbook1. Now close Workbook1.

Just sit in Workbook2 for about a minute. Betcha you see an error. :friends:

:devil:

[Paleo]

Gee, that was unexpected. But, well, you are right as usual. I will use that Workbook_BeforeClose then.

Thanks again

[Paleo]

Hi Ken,

made the change and it works great now. Thanks again.

[mdmackillop]

30 minutes inactivity? Seems like your tea breaks are way too long!!!

[Paleo]

Yeap, it was 10 minutes but people asked me to make it longer. They must be dating some data in the workbook to stare so long looking at it...:dunno

[Ken Puls]

Hi Carlos,

You're welcome!

I'm fairly certain (it's been a while since I read Chip's page) that the way this one actually works is to schedule the event to be called later. The problem is that it stays in Excel's memory until the thing is turned off, so even if the workbook is closed, it stills hangs out there.

For the benefit of anyone else reading this, that's why I recommended the Deactivate events as well. If you don't want it to fire when you activate a different workbook, you will want to turn it off from one of those events.


30 minutes inactivity? Seems like you tea breaks are way too long!!!

:rotlaugh:

[Paleo]

Dang, here I go alter it again, but as you were right before I wont even argue.

Altering it.....

[Ken Puls]

Well hang on! :dousing:

If you want the workbook to close even if someone is in another one, it's fine as is. It's only if you don't want it to close that you'd need to modify it!

(Sorry, best smilie there was for the situation!) :rotlaugh:

[Paleo]

Oh no, then it was right. I want it to close even if someone is in another one.

[Ken Puls]

That's what I thought you meant. Sorry, I was just trying to make it obvious for anyone else who was trying to learn from this post.

I've played with the ontime method for different reasons, so I wouldn't want it to keep going if a user opened another wb.

Your code should be good as is. :yes

[Paleo]

Hi Ken,

this is the final code. I have submited it to the KB to help others.

At This_Workbook:



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Limpa
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call Timer
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
' If you dont want to close the workbook while you are using another one
' simply enable this by removing the " ' " before "Call Limpa"
' if you dont do that the application will be closed even if you are
' working at another workbook
'Call Limpa
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call Timer
End Sub


At Module1:

Public vartimer As Variant

Sub Timer()
Call Limpa
vartimer = Format(Now + TimeSerial(0, 30, 0), "hh:mm:ss")
If vartimer = "" Then Exit Sub
Application.OnTime TimeValue(vartimer), "Fecha"
End Sub
Sub Fecha()
With Application
.EnableEvents = False
ActiveWorkbook.Save
.Quit
End With
End Sub
Sub Limpa()
On Error Resume Next
Application.OnTime earliesttime:=vartimer, _
procedure:="Fecha", schedule:=False
On Error GoTo 0
End Sub