Scroll rows in a wb that displays a scoreboard

[Clay Shooter]

I have workbook that displays a scoreboard generated by values form another workbook. The number of scores (rows) is greater than the visibble screen area, so I need to scroll the worksheet slowly to allow viewers to see the scores. I need this to happen without freezing excel as I need to be updating scores in the source workbook on the fly, so Application.Wait will not do. Any suggestions please?

[buran]

Use Application.OnTime to call subroutine that will scroll

[Clay Shooter]

Thanks buran, I have used Application.OnTime to call a procedure "Scroll_Rows", and it works. But the start time for Application.OnTime will not update, so all the rows are scrolled instantly instead of one row at a time after a delay - which I have set as "UpdateInterval2". See my code below -

Please Login or Register  to view this content.

How do I get RunTime2 to update for each time the "Scroll_Row" procedure is called?

Thanks.

[buran]

Please, use Code tags around your code as per the forum rules.

[Clay Shooter]

Sorry, thought I did that - see edited post above.
Thanks

[buran]

Hi Clay, thanks

the problem with your code is in that line

Please Login or Register  to view this content.

The entire loop takes less than a second, so you set all the calls to "Scroll_Row" sub within 1 second.
Change your code like this

Please Login or Register  to view this content.

Note that once you start it, you cannot disable the scroll unless you store each RunTime somewhere and set schedule = False for each scheduled run.

[buran]

Also note that I pass the row number as argument to Scroll_Row. Change that if you have different Scroll_Row sub

[Clay Shooter]

Thanks Buran, sorry for he 12 hour delay in posting responses, but I am that many time zones away from you in Europe.
I see exactly how you have modified my RunTime2 variable - and it works just like I wanted. I also see how you have created the row variable into my Scroll_Row sub so that it changes its name each time it is called. This works too, but I can't see how you set the value of lngScrollRow.
The macro seems to stop running when it has reached the i = Lr value, but I understand that the macro might be continuing to run because it has not been cancelled.
I have written some code (below) that I think should cancel the Application.OnTime but it returns the Method 'OnTime' of object _Application' failed error. It does this even if I put the code in the Scroll_Row macro.
Does the problem have something to do with how the lngScrollRow variable is defined?

Please Login or Register  to view this content.

Your help is appreciated.

[buran]

Hi.

When you use OnTime method you schedule number of calls to Scroll_row sub. the main sub Display_Scores will finish in a second or two (depends how many rows you have, i.e. Lr-11 number of rows). When it finish you have scheduled Lr-11 times runs of Scroll_Row macro, each of them separated in time by UpdateInterval2 seconds. So to cancel, you should cancel each individual scheduled run with the same statement that you used to schedule it and only last argument set to False. That's why I made the note.

As an alternative you can use recursion and make Scroll_Row schedule the next run. This way at any point of time you will have only one scheduled run. here is sample code:

Please Login or Register  to view this content.

I would recommend this approach. You can call off next run using the statement

Please Login or Register  to view this content.

[buran]

I just didn't reply to your question about how I set lngScrollRow variable. It's argument to the sub and I pass it here

Please Login or Register  to view this content.

Note the i and also the single quotes.