Printable View
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?
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)[VBA]Option Explicit
Dim t As Date
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ScheduleEvent
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ScheduleEvent
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 Sub[/VBA]And the called procedure (in a standard module) - mine just switches between sheet 1 and 2[VBA]Sub ChangeSheet()
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet2").Activate
Else
Sheets("Sheet1").Activate
End If
End Sub[/VBA]
Hope that helps