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

genepaton

New Member
Joined
Jun 1, 2011
Messages
37
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try something like this...

Code:
    Dim WaitTime As Single

    WaitTime = Timer + 5    'Wait 5 seconds
    Do While Timer < WaitTime
        DoEvents    ' Yield to other processes.
    Loop
 
Upvote 0
Try something like this...

Code:
    Dim WaitTime As Single

    WaitTime = Timer + 5    'Wait 5 seconds
    Do While Timer < WaitTime
        DoEvents    ' Yield to other processes.
    Loop

Hi AlphaFrog,

Thanks for that.
I tried substituting this script with the
Application.Wait Now + TimeValue("00:00:05")
but it seems to be jumping (eg less than 5 seconds).
To my limited knowledge, aren't loops best avoided?

The active X elements, are just to update a WebBrowser object;

Code:
Private Sub Worksheet_Activate()
    
On Error Resume Next

    Me.ScrollArea = "A1:T48"

    WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
    WebBrowser1.Document.body.Scroll = "no"

End Sub
So how would you incorporate your code?

Code:
Private Sub Worksheet_Activate()

Dim WaitTime As Single

    WaitTime = Timer + 5    'Wait 5 seconds
    Do While Timer < WaitTime
        Do WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
    WebBrowser1.Document.body.Scroll = "no"
    Me.ScrollArea = "A1:T48"

End Sub
???
 
Upvote 0
Code:
     WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
    ' Wait while WebBrowser1 is busy...
    Do While WebBrowser1.Busy
        DoEvents
    Loop
    WebBrowser1.Document.Body.SetAttribute "scroll", "no"
    Me.ScrollArea = "A1:T48"
 
Upvote 0
Hi Alpha,

No luck with that either.
I need something that will progress to the next sheet, or that can run while Application.Wait is running. I tried yours on the worksheet activate, but if im still using the Application.Wait to stop on the sheet and then progress, it doesn't allow the browser to load. Also for some reason, the page that it links to didn't like the script being changed. It presented a 404 message, and yet if i leave the script how i had it, it works. The web address is identical but. Weird.


Code:
     WebBrowser1.Navigate2 "http://www.calculatorcat.com/moon_phases_/phasenow.php?tcv=31"
    ' Wait while WebBrowser1 is busy...
    Do While WebBrowser1.Busy
        DoEvents
    Loop
    WebBrowser1.Document.Body.SetAttribute "scroll", "no"
    Me.ScrollArea = "A1:T48"
 
Upvote 0
"Progress to the next sheet" is too vague. What triggers the browser to load and what else do you want to do while the browser is loading? Please be specific, detailed, and include code if you can.
 
Upvote 0
Sorry,

I have loaded in the Worksheet Open a macro which does various things and then use the Sheet#.Activate and Application.Wait commands to essentially present a slide show of the 7 sheets in my workbook. eg.

Sheets(4).Activate
Application.Wait (00:00:05)
Sheets(5).Activate
Application.Wait (00:00:05)
etc

This macro is scheduled to run every 15 min with a Application.OnTime script at the end of it.
The problem being, that as say the 6th sheet that has the microsoft browser object to display the lunar cycle, when it progress's to that sheet because the macro is using the Application.Wait it doesn't allow the web browser object to load. Therefore you get a blank browser window. The browser object is embedded on the sheet, so therefore if you manually select it, it refreshes itself. There's obviously a short delay of ~1 second for it to refresh the browser frame.

I was looking for a VBA solution that causes the macro to pause on that page (sheet), allows the browser to load, and then progress to the next sheet (think of powerpoint).
I had the thought today that i could just schedule multiple OnTime events, but this has the potential to be very messy. I can provide you with the coding tomorrow if you want to see an example?

"Progress to the next sheet" is too vague. What triggers the browser to load and what else do you want to do while the browser is loading? Please be specific, detailed, and include code if you can.
 
Upvote 0
The worksheet with the browser is called "Lunar"

Code:
Sub Worksheet_SlideShow()
    
    Dim ws As Worksheet, t As Single, countdown As Integer
    
    For Each ws In Worksheets
    
        ws.Activate
        t = Timer + 5   '5 second timer
        
        If ws.Name = "[COLOR="Red"]Lunar[/COLOR]" 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
        
        'Wait 5 seconds
        Do While Timer < t
            DoEvents ' Allows the browser to update and other events to complete
            
            'Status bar countdown
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
        
    Next ws
    Application.StatusBar = "Done"
    
End Sub
 
Upvote 0
That works great! Thanks for that.
Im just trying to work how i'd adapt it to the macro i already have.
I discovered that you don't even need to have the ActiveSheet.WebBrowser included in this macro, as you can leave it in the worksheet.activate code for that particular sheet. It looks like the wait timer allows it to refresh/load where as the Application.Wait didn't.
The only downside to your code is that it cycles all worksheets as a slide show, how would you have it apply to only sheet 4 onwards?

Here's the end of the macro that i already have;

Code:
EndMacro:

    DoEvents
    ThisWorkbook.Sheets(4).Activate
    Application.ScreenUpdating = True
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(5).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(6).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(7).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(4).Activate
Else
    
    ThisWorkbook.Sheets(4).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(5).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(6).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(7).Activate
    Application.Wait Now + TimeValue("00:00:05")
    ThisWorkbook.Sheets(4).Activate
        
End If
   
   Application.OnTime dTime1, "TestImportData"
   
End Sub
The EndMacro is an error handler, and the whole 'If' string is "Do If" a particular file is available to pull new data from every 15 min, otherwise just start the slideshow again.

I like your idea of having the waiting time shown on the status bar, but as mentioned i don't know how i'd limit the cycled sheets and have the wait time shown.

The WebBrowser object is in Sheets(6)

Thanks for this hey!

G


The worksheet with the browser is called "Lunar"

Code:
Sub Worksheet_SlideShow()
    
    Dim ws As Worksheet, t As Single, countdown As Integer
    
    For Each ws In Worksheets
    
        ws.Activate
        t = Timer + 5   '5 second timer
        
        If ws.Name = "[COLOR=Red]Lunar[/COLOR]" 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
        
        'Wait 5 seconds
        Do While Timer < t
            DoEvents ' Allows the browser to update and other events to complete
            
            'Status bar countdown
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
        
    Next ws
    Application.StatusBar = "Done"
    
End Sub
 
Upvote 0
Actually i think i worked it out. It just may be a little messy.

Code:
EndMacro:

    ThisWorkbook.Sheets(4).Activate
    Application.ScreenUpdating = True
    t = Timer + 5
    Do While Timer < t
            DoEvents 
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
    
    ThisWorkbook.Sheets(5).Activate
    t = Timer + 5
    Do While Timer < t
            DoEvents 
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
    
    ThisWorkbook.Sheets(6).Activate
    t = Timer + 5
    Do While Timer < t
            DoEvents 
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
    
    ThisWorkbook.Sheets(7).Activate
    t = Timer + 5
    Do While Timer < t
            DoEvents 
            If Int(t - Timer) + 1 <> countdown Then
                countdown = Int(t - Timer) + 1
                Application.StatusBar = "Waiting: " & countdown & " seconds"
            End If
            
        Loop
    
    ThisWorkbook.Sheets(4).Activate

Else
     
'blah
'blah
'blah

End If
   Application.StatusBar = "Done"
   Application.OnTime dTime1, "TestImportData"
   
End Sub

Do i need to repeat each string so much? It seems to be working...
 
Upvote 0

Forum statistics

Threads
1,224,570
Messages
6,179,608
Members
452,930
Latest member
racefanjtd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top