Cannot Stop Scrolling

Do you want to be able to adjust the scroll on-the-fly?

Go under the developer -> properties -> (sheet properties) "Scroll Area" (set it from the top left to bottom right active cells, i.e. $A$1:$G$20), that will prevent you from scrolling out of those bounds.

.scrollarea is the property for the "worksheets" class, and you can integrate a dynamic scroll area if you want it to be automated.

Thanks for your prompt response. I inherited the workbook and so I am not familiar with the VBA coding
for the scrolling of the scoresheet. I am a relative novice for coding and so I do not understand your
advice (my problem not yours). The number of teams can be between 1 and 30(max) and so I hide the rows
for those teams not needed. How would this work for your suggested solution?

Do you know how long you want it to scroll for? As written it scrolls from top to bottom and back again, then repeats 10000 times unless cancelled. It would be quite easy to add in either an amount of times to scroll up and down, or a time limit, if that's what you want to do?

I use the scrolling function after each round of the quiz. I would be happy to set the scrolling time
for a set period (say 2 minutes). If I need to I could extend the scrolling time by clicking on the
scroll button again.

Try this then. I've put the new bits in red and removed a couple of redundant lines.

Please Login or Register  to view this content.

Many thanks, its getting late and so I will give that a go tomorrow and let you know how I got on.

Thanks for your help - that works. I modified your suggestion to provide
an adjustable value for the time that the scoresheet scrolls (rather than
a fixed 120 seconds)

Please Login or Register  to view this content.

I have uploaded the modified workbook so you can see how it works.

To simplify the use of this workbook for someone who is not too familiar with its use,
I would rather use a macro that I could attach to a button on the the Summary worksheet
that does the same as the Ctrl-ScrLk key combination. I tried to record a macro to
achieve this but as you will see from the Scroll_Stop macro it did not work (no code).
Do you know the code I could use?

It might be possible, but I don't know how to let a user press a button to stop a macro. You could for example get a MsgBox to pop up after each iteration and/or time period asking whether you want to keep scrolling?

Nearly there! When I toggled the Scroll button On it started to scroll but when I toggled back to turn scrolling off it reset the list but continued to scroll. Hopefully there is a simple fix for this problem.
Thanks again for your assistance.

Hmm, try this out (attached). I modified the code section where it checks if the scroll option is on.

Note: While the button says "Scroll on" it should run for the defined period of time, while the button says "Scroll Off" the document won't scroll. If you want to scroll again, click the button on and off (you may want to set it back to infinite scrolling, and use the toggle button to determine whether its on or off).

Disregard my last post. I modified your original code & it works perfectly now. I didn't realize that the scrolling was messed up - I saw the sheet moving and thought it was okay.

I had to adjust the "D27 value" on the live score sheet to meet the condition to scroll - not sure why it's constrained by a random team's score, but I'm sure you're familiar with that condition.

Please see attached. Thanks.

I saved the file under a different name but the macros still reference the previous version. That appears to have prevented the Sort button from working correctly
(it scrambles the names). I have uploaded the file with the new filename so that you can see what happened. Also, the scroll function did not stop until I toggled
the scroll button. The original worksheet scrolled to the bottom of the names and then stepped back to the beginning. Would it be possible to retain the functionality
of the original file and add the toggle function for scrolling? Sorry to persist - perhaps I have not made myself understood.

I have also attached the original workbook for comparison of the functionality. The only thing that did not work was not being able to cancel the scrolling using the Ctrl-Scrlk key function because the laptop keyboard does not have the ScrlLk key.

Originally Posted by kencoburn

I have also attached the original workbook for comparison of the functionality. The only thing that did not work was not being able to cancel the scrolling using the Ctrl-Scrlk key function because the laptop keyboard does not have the ScrlLk key.

Thank you! I was about to ask for the original... I couldn't get any of the above codes to loop back to the start. I'll integrate the toggle button into your original workbook

It's working exactly how you'd like it (I hope)!! Let me know if it works for you :D

When I toggle the scrolling off I want the worksheet to go back to the top of the list. Also I don't want the scrolling to continues past the list of players. The original workbook will show you how it worked and was able to account for hiding any rows of the 30 that were not taken by players. The values in the cells below the list of players allowed me to adjust the number of rows that were scrolled.

Just a thought. Is there not a macro that could be written that does the same function as Ctrl-ScrLk? As I said earlier, I tried to record such a macro but ended up with a blank function?

Thanks, I must have downloaded and run an earlier file. I will give it another go tomorrow.

A slightly different solution (or even two).
If all visible rowss fit on the screen, there will be no scrolling. There is no definition of the number of visible rows. They are calculated by macro.
Ctrl+w breaks the macro.

I don't think it's a better version. Just different.

Artik

I like the improvement in determining the number of visible rows (if the numbers of teams cannot be displayed on one screen). Version 2 is probably the easiest for the novice user - it saves the novice user having to input number of players and number of visible rows.

The comment in the macro states 30 secs timing before it stops scrolling. I changed the "Scroll Time (Seconds) t0 60 and this extended the scrolling period. Was this your intention?

There are numbers to the right of the table - can I remove these numbers?

I will answer YES to the last two questions.

Artik

Thanks again, you have been a great help. I inherited the original Quiz spreadsheet and needed help because I only have a basic knowledge of VBA.

I tried to help; sorry I missed the mark!

Just to finish everything off in case it is useful for anyone else, I have uploaded
the final version that I will use for our Quiz Night. Many thanks to those who
contributed to getting my original workbook to the more user friendly version.