Screen Scrolling Macro (VBA) freezes

Office 2010 - MS Excel

I was provided with the following macro (VBA) to scroll a MS Excel spreadsheet on the screen. The macro is activated by a control button on the screen and on about half the occasions it works - the spreadsheet which is actually a quiz score sheet - scrolls up the screen and then down the screen until I press the Escape key on my keyboard. However, on about half the occasions the spreadsheet freezes with the message "Quiz Score Sheet not responding" and everything is greyed out. The only way to get out of the program is to use the Ctrl-Alt-Del keys.

The values n, sec and viz are read from a second page (currently set at 20, 2 and 10).

The problem accurs on both a Vista and Windows 7 computer.

Is there anything obvious that I can change to make the macro more stable?

=====================================

Please Login or Register  to view this content.

========================================

You might try scattering the statement DoEvents throughout the code (particularly in the loop) to see if this helps.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

You need to have the statement ["CODE"] your code ["/CODE"] without the quotation marks before and after your vba code. This will convert it automatically to the proper html style. If you are going to use the button, then highlight your code and press the vba code button. This should put the CODE /CODE delimiters around your code.

Good luck and thanks for abiding by the rules.

Unusually, it doesn't really matter where you insert the statement. DoEvents just pauses the processing in Excel to allow other things happening on your compter to take place an this seems to help prevent freezes. Its often trial and error. I would suggest trying inside your While .. Wend loop in the first instance.

I have introduced the DoEvents statement in two places in the loop and that appears to have worked - the scrolling of the screen does not freeze. Initially I could not stop the scrolling by pressing the Escape key briefly but then found that if I kept the key pressed a little longer (ie.e did not just tap the key) it stopped the scrolling.

Many thanks for your help.

Please Login or Register  to view this content.

Maybe try putting this at the beginning and end of you code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

There is another method that I use sometimes especially, especially as a cancel button on userforms. I've stripped it down and added your code. Take a look and let me know if it works. If you are going to copy it to another workbook then take care to also copy the code from the Userform.

Control - Break sometimes works if escape doesn't.

Glad it worked out. If you are satisfied with the responses then mark the thread as solved.