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

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!

[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.

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...

[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? )

You know, sometimes I just feel I kinda hate the user and start punishing he/she
. (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.

[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...

[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!!!

[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!

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!)

[Paleo]

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