VBA code to make sheet wait and then progress without using Application.Wait???

[genepaton]

Hi,

Im wondering if there is VBA code to make a worksheet activate, wait for a given period and then progress to the next sheet, without using the script Application.Wait?

I need to avoid using this so that any Active X controls i have on the page can update.

I've tried playing with Application.OnTime and using another macro to advance a sheet like Sheets(ActiveSheet.Index + 1).Activate, but i can't seem to get it right.

Any help greatly appreciated.

 

[AlphaFrog]

This might give you some ideas on how to clean it up...

EndMacro:

    Call Worksheet_SlideShow
   
    Application.OnTime dTime1, "TestImportData"
   
End Sub

Private Sub Worksheet_SlideShow()

    Dim i As Integer, ws As Worksheet, t As Single, countdown As Integer

    For i = 4 To Sheets.Count
    
        Set ws = Sheets(i)
        ws.Activate
        t = Timer + 5   '5 second timer

        If i = 6 Then
            ActiveSheet.WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
            ActiveSheet.WebBrowser1.Document.Body.SetAttribute "scroll", "no"
            ws.ScrollArea = "A1:T48"
        End If


        Do While Timer < t 'Wait 5 seconds
            DoEvents ' Allows the browser to update and other events to complete

            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds" 'Status bar countdown
            End If
        Loop

    Next i
    Application.StatusBar = "Done"

End Sub

 

[genepaton]

Brilliant, thank you very much.
Code works great, and looks a lot neater.
So the Wait Timer allows things to load/you to interact with things on the page, where as the Application.Wait keeps the hourglass on screen and no interaction.

This might give you some ideas on how to clean it up...

EndMacro:

    Call Worksheet_SlideShow
   
    Application.OnTime dTime1, "TestImportData"
   
End Sub

Private Sub Worksheet_SlideShow()

    Dim i As Integer, ws As Worksheet, t As Single, countdown As Integer

    For i = 4 To Sheets.Count
    
        Set ws = Sheets(i)
        ws.Activate
        t = Timer + 5   '5 second timer

        If i = 6 Then
            ActiveSheet.WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
            ActiveSheet.WebBrowser1.Document.Body.SetAttribute "scroll", "no"
            ws.ScrollArea = "A1:T48"
        End If


        Do While Timer < t 'Wait 5 seconds
            DoEvents ' Allows the browser to update and other events to complete

            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds" 'Status bar countdown
            End If
        Loop

    Next i
    Application.StatusBar = "Done"

End Sub

 

[AlphaFrog]

Brilliant, thank you very much.
Code works great, and looks a lot neater.

You're welcome.

So the Wait Timer allows things to load/you to interact with things on the page, where as the Application.Wait keeps the hourglass on screen and no interaction.

Yes.