Pause

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
133
The following macro is supposed to display the scores of a competition on a screen, moving down the scores until all have been displayed.
It worked fine in Excel 2003 but in 2007 or 2010 the screen does not refresh after the cursor has moved down to the fouth or fifth line of the results.

Sub LoopDisplayView()
Dim PauseTime, Start, Finish, TotalTime
Do Until ActiveCell = ""
If ActiveCell = "" Then
Exit Sub
Else
PauseTime = Range("display!AA4") ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop
End Sub

The above is a subroutine of a macro which then sorts the results into categories and classifications and shows all these before starting again. any idea what I am doing wrong?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Your code worked for me in Excel 2007. I used a pause of 1 second and 10 rows of data. Maybe try:

Rich (BB code):
Sub LoopDisplayView()
    Dim PauseTime, Start, Finish, TotalTime
    Do Until ActiveCell = ""
        If ActiveCell = "" Then
            Exit Sub
        Else
            PauseTime = Range("display!AA4") ' Set duration.
            Start = Timer ' Set start time.
            Do While Timer < Start + PauseTime
            Loop
            Finish = Timer ' Set end time.
            TotalTime = Finish - Start ' Calculate total time.
            DoEvents
            ActiveCell.Offset(1, 0).Range("A1").Select
        End If
    Loop
End Sub
 

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
133
Andrew,

My mistake, my macro was stopping on line 11 or 12 so it might not have had a problem with your 10 lines of data.
Adding in the DoEvents has resolved the problem so thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,120
Messages
5,466,809
Members
406,499
Latest member
ToyoMike

This Week's Hot Topics

Top