Trigger code after inactivity

Question

I want my WB to trigger some code after inactivity time (set by me, naturally). I could only find code to close the WB after inactivity time, but I want the code to do something else, different from closing the WB. I found this code for closing the WB:

This Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    stop_Countdown
ThisWorkbook.Save
End Sub
Private Sub Workbook_Open()
    start_Countdown
    End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    stop_Countdown
    start_Countdown
    End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    stop_Countdown
    start_Countdown
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    stop_Countdown
    start_Countdown
End Sub

Regular module:

Option Explicit
Public Close_Time As Date
Sub start_Countdown()
    Close_Time = Now() + TimeValue("00:00:10")
    Application.OnTime Close_Time, "close_WB"
    End Sub
Sub stop_Countdown()
    Application.OnTime Close_Time, "close_WB", , False
    End Sub
Sub close_wb()
    ThisWorkbook.Close True
    End Sub

In what part of the code should I introduce the events I want the WB to do, after inactivity, instead of closing the WB?

Make sure `Application.EnableEvents = true` when calling `Workbooks.Open` — Seb, Jan 20 '16 at 21:27
sorry, the question wasn't written properly. this is what I need help with, please read it again. — N. Pavon, Jan 20 '16 at 21:46
were you able to solve your issue? If yes, accept the answer that helped you or create your own answer. Keep us updated! — Oscar Anthony, Jan 22 '16 at 15:41
@brettdj Those are two **different** problems, even if portion of the same code appear in both. One is about the issue of automatically **closing** a workbook **when opened from a macro of another workbook** and the second one is about **automatically doing something else after a certain time of inactivity** — Oscar Anthony, Jan 22 '16 at 19:24
@OscarAnthony Both questions by the same author are about running events after a period of activity. — brettdj, Jan 23 '16 at 01:26
but it's a different question, because closing a WB only needs to be done once, triggering a procedure every time the user is inactive and resetting it after activity is a different matter — N. Pavon, Jan 23 '16 at 01:29

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

You need to make changes in the Regular module.

Instead of passing the String close_wb() in your Application.OnTime function call, you have to specify the name of the procedure containing whatever you want to perform.

Here is the code to get you started:

Option Explicit
Public Inactivity_Time As Date

Sub start_Countdown()
    Inactivity_Time = Now() + TimeValue("00:00:10")
    Application.OnTime Inactivity_Time, "my_procedure"    ' <- Notice that I changed the name of the procedure here
End Sub

Sub stop_Countdown()
    On Error Resume Next
    Application.OnTime Inactivity_Time, "my_procedure", , False     ' <- And here too.
    On Error GoTo 0
End Sub

Sub my_procedure()
    ' The code to perform you event goes here
End Sub

Please check here for more details on the Application.OnTime method.

EDIT: After some test, it appears that you can't call stop_Countdown() in the Workbook_BeforeClose sub procedure: It throws an error. According to this post, in your Workbook module, you have to replace the procedure Workbook_BeforeClose with the following one:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Close_WB
End Sub

And add the following procedure:

Public Sub Close_WB()
    stop_CountDown
    ThisWorkbook.Close SaveChanges:=True
End Sub

edited May 23 '17 at 11:45

Community

1
1

answered Jan 20 '16 at 22:47

Oscar Anthony

190
3
18

should I change something else, besides the close_wb() procedure? what I mean is, where do I call the procedure myProcedure()? – N. Pavon Jan 20 '16 at 22:57
You do not actually need to manually call `my_procedure()`. Excel automatically runs the procedure specified in the string argument of the function `Application.OnTime`. If you want for example to run another procedure instead, you just need to replace the `"my_procedure"` part in the function calls of `Application.OnTime` with the name of the other procedure you want. – Oscar Anthony Jan 20 '16 at 23:21
and the code in This Workbook remains the same? – N. Pavon Jan 20 '16 at 23:28
I did it and I got this error after closing the workbook: – N. Pavon Jan 20 '16 at 23:37
Runtime error '1004' – N. Pavon Jan 20 '16 at 23:37
Method 'OnTime' of object '_Application' failed – N. Pavon Jan 20 '16 at 23:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/101233/discussion-between-oscar-anthony-and-n-pavon). – Oscar Anthony Jan 20 '16 at 23:56
This question is different from the one marked as duplicate1 – N. Pavon Jan 22 '16 at 17:17
@N.Pavon I made the final changes (to the `Close_WB` and `stop_Countdown` procedures). It should be wokring now! please can you test with the new code in my answer? – Oscar Anthony Jan 22 '16 at 19:18

Trigger code after inactivity

1 Answers1