View Full Version : Solved: Timed Loops

05-23-2006, 10:29 PM
hi all,
Is it possible to do a timed loop?

I wan to do somethinglike this: waiting for an input in let's say cell A1(sheet1).
If there is no input for 30seconds, it jumps to another spreadsheet, and after another 30seconds jumps back to the current worksheet(sheet1) and waits for input again.

Is there such syntax?

05-24-2006, 02:04 AM
You can use the Application's "OnTime" method to schedule a procedure that switches sheets. However, you will need to cancel the scheduled event if the user intervenes (by changing the sheet or entering data) then reset the timer.

So I think a solution would be to have an event scheduleing procedure (that first cancels the last scheduled event) and call it from the appropriate workbook events.

I'm not sure how reliable this will be in practice but I suppose it's worth trying. Here's some code for the "ThisWorkBook" module. (I've left in my debug commands so you can see the activity in the Immedeate window of the VBE)Option Explicit

Dim t As Date

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
End Sub

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

Private Sub ScheduleEvent()
On Error Resume Next
If t > 0 Then
Application.OnTime EarliestTime:=t, Procedure:="ChangeSheet", Schedule:=False
Debug.Print "Cancelled: " & t
End If
t = Now() + TimeValue("00:00:30")
Application.OnTime EarliestTime:=t, Procedure:="ChangeSheet"
Debug.Print "Scheduled: " & t
End SubAnd the called procedure (in a standard module) - mine just switches between sheet 1 and 2Sub ChangeSheet()

If ActiveSheet.Name = "Sheet1" Then
End If

End Sub

Hope that helps