-1

I have the below code which works well to display each defined worksheet for 20 seconds, in a loop. I would like to be able to refresh the workbook after each loop or at a defined interval. Inserting ActiveWorkbook.RefreshAll in the code doesn't seem to work.

Any help would be hugely appreciated!

Many thanks in advance

Sub Test2()

    Dim i As Long, j As Long, t As Single, str As String
    Dim MyArray As Variant
    MyArray = Array("Sheet1", "Sheet2", "Sheet4", "Sheet6")
    For j = LBound(MyArray) To UBound(MyArray)
        On Error GoTo exit_
        Application.EnableCancelKey = xlErrorHandler
        str = MyArray(j)
        Sheets(str).Select
        t = Timer + 1
        Application.Wait Now + TimeSerial(0, 0, 20)
        If Timer < t Then Exit Sub
        If j = UBound(MyArray) Then
            j = -1
        Else
            j = j
        End If
    Next j
exit_:

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
ERZUT
  • 15
  • 7
  • You might want to have a look at this: https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba – DecimalTurn Jan 17 '18 at 23:57
  • Thank you @DecimalTurn - That was really helpful! I added: Activeworkbook.RefreshAll DoEvents and disabled background refresh in my connections. – ERZUT Jan 18 '18 at 01:02
  • Possible duplicate of [Wait until ActiveWorkbook.RefreshAll finishes - VBA](https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba) – DecimalTurn Jan 18 '18 at 04:21
  • @DecimalTurn Yes it seems that the workbook refreshes before it goes to the next sheet instead of after the entire loop. I checked out the link you posted but still can't figure out how to do this. If you could provide any further insight, it would be much appreciated! Thanks – ERZUT Jan 22 '18 at 05:02
  • If I understand your question correctly, to do it at the end of one loop, you would have to put `ActiveWorkbook.RefreshAll` and `DoEvents` right before `Next j` – DecimalTurn Jan 22 '18 at 15:20
  • @DecimalTurn - Apologies for the delayed response! I have just tried that but the refresh doesn't work at all. It just loops through all the sheets. Thanks again for the help :) – ERZUT Jan 29 '18 at 23:02
  • I'm a bit confused about what you are really trying to do. Are you actually trying to refresh the sheet and let the user look at the refreshed sheet for 20 sec? – DecimalTurn Jan 29 '18 at 23:09
  • The workbook will be displayed on a screen in our office. There are 4 x sheets (with various reports) that we would like displayed for 20 seconds at a time (in a loop). We have several external data sources that require frequent refreshing. I hope this help explain - thanks again for your help on this – ERZUT Jan 30 '18 at 02:40
  • @DecimalTurn - apologies, I forgot to tag you in the above comment. Any insight would be highly appreciated! – ERZUT Feb 02 '18 at 03:51
  • I think I misunderstood what you meant by "after the entire loop". If you want to show all 4 sheets (20 sec each) and then refresh, you would need to put it inside the if statement right after`j = -1` – DecimalTurn Feb 02 '18 at 04:20

1 Answers1

0

Thanks to DecimalTurn's assistance, the following code worked after disabling background refresh:

Sub Test2()

 Dim i As Long, j As Long, t As Single, str As String
 Dim MyArray As Variant
 MyArray = Array("Sheet1", "Sheet2", "Sheet4", "Sheet6")
 For j = LBound(MyArray) To UBound(MyArray)
    On Error GoTo exit_
    ActiveWorkbook.RefreshAll
    DoEvents
    Application.EnableCancelKey = xlErrorHandler
    str = MyArray(j)
    Sheets(str).Select
    t = Timer + 1
    Application.Wait Now + TimeSerial(0, 0, 20)
    If Timer < t Then Exit Sub
    If j = UBound(MyArray) Then
        j = -1
    Else
        j = j
    End If
Next j
exit_:

End Sub
ERZUT
  • 15
  • 7