Consulting

Results 1 to 2 of 2

Thread: Solved: Timed Loops

  1. #1

    Solved: Timed Loops

    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?

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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
    K :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •