Timed Sub

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?

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

Code:

---

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

---

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:

Code:

---

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

Hi guys,

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

Problem solved!

Quote:

---

Originally Posted by Paleo

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

Quote:

---

Originally Posted by Paleo

Problem solved!

---

So what was the answer? :*)

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

Here is it:

At This_Workbook

Code:

---

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

Code:

---

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

---

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

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

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

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)

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:

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

Thanks again

Hi Ken,

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

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

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

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.

Quote:

---

Originally Posted by mdmackillop

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

---

:rotlaugh:

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

Altering it.....

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:

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

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

Hi Ken,

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

At This_Workbook:

[VBA]

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
[/VBA]

At Module1:
[VBA]
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
[/VBA]