VBA Excel Application OnTime after 1 minute activity

Question

I have created a file and have a code, which after 1 minute regardless of where I am in the ActiveSheets goes to select the default cells that I indicated in the code.

Please find my code below:

This Workbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
stop_timer
End Sub

Private Sub Workbook_Open()
start_timer
End Sub

Regular Module:

Option Explicit
Public RunWhen As Double
Public Sub start_timer()
'active the code after 1 minute
RunWhen = Now + TimeValue("00:01:00")
Application.OnTime RunWhen, "TimerRoutine"
End Sub

'Hier the code that must run every 1 minute and what he has to do'
Public Sub TimerRoutine()
Select Case LCase(ActiveSheet.Name)
Case "intervento spinale"
    ActiveSheet.Range("J97").Select
Case "stroke"
    ActiveSheet.Range("J131").Select
Case "infiltrazione"
    ActiveSheet.Range("J67").Select
Case "diagnostica"
    ActiveSheet.Range("J97").Select
Case "embo"
    ActiveSheet.Range("J136").Select
Case "epatobiliare"
    ActiveSheet.Range("J88").Select
Case "body"
    ActiveSheet.Range("J151").Select
End Select

start_timer  ' Reschedule the procedure
End Sub

Public Sub stop_timer()
Application.OnTime RunWhen, "TimerRoutine", , False
End Sub

My problem is that this code always works, even if I'm working on the sheets. after 1 minute it automatically goes into the cell indicated by the code. I would like if I'm working on the sheets, the code does not start to count the 1 minute.

Only if I stop, and I stay still for 1 minute after executes the code. But if I work, I write in the cell, then no. How do I change my code created to get after the inactivity the minute countdown to start my code?

score 0 · Answer 1 · answered Oct 14 '18 at 19:18

In the ThisWorkbook module you can do something like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    stop_timer
    start_timer  
End Sub

This will reset the timer whenever you change the selection on any sheet in the workbook.

VBA Excel Application OnTime after 1 minute activity

1 Answers1