A Macro For Continuous Screen Scrolling?

[minion]

Mr. Excel,

I have a large list of dart tournament contestant names displaying the opponents and the board the match will be played upon. If I zoom the entire list on the monitor, people can't read the small text. Is there a formula or a macro that will slowly scroll down the list? Ultimately, I would like to press a button and have the screen slowly scroll down to the bottom of the list and then scroll back up to the top of the worksheet continuously until I press the button again. Can the scroll speed be varied? I have skimmed through several VBA reference manuals and this issue is not addressed.

An example of the continuous scrolling would be pressing down on a mouse with a wheel.

Thanks for your time!

minion

 

[Devin]

This code did the job, but you can probably do better. I am still new at this.

Sub SlowScroll()
For i = 1 To 30
r = 1
Do Until r = 5000
Cells(r, 30) = r
r = r + 1
Loop
ActiveWindow.SmallScroll Down:=1
Next i
Range("A1").Select
SlowScroll
End Sub

 

[Kenneth Hobson]

You can adjust the sleep time if needed to speed or slow scroll. Put this in a Module. Of course the mouse wheel method is more smooth.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public inc As Integer

Sub ScrollNow()
  Dim lastRow As Long, nextRow As Long
  Application.ScreenUpdating = True
  
  lastRow = ActiveSheet.UsedRange.Rows.Count
  If inc <> 1 Or inc <> -1 Then
    If ActiveCell.Row = lastRow Then
      inc = -1
      Else: inc = 1
    End If
  End If
  
  On Error GoTo handleCancel
  Application.EnableCancelKey = xlErrorHandler
  
  Application.StatusBar = "To End: ESC, Ctrl+Break"
  Do While 1 = 1 'infinite loop
    If inc = 1 And lastRow = ActiveCell.Row Then inc = -1
    If inc = -1 And ActiveCell.Row = 1 Then inc = 1
    nextRow = ActiveCell.Row + inc
    Application.Goto Range("A" & nextRow), True
    Sleep 400
  Loop
handleCancel:
  Application.StatusBar = False
End Sub

 

[minion]

Kenneth Hobson,

Thank you! I copied the macro into its own module and it worked. It selected cell A1 and continuously scrolled down the worksheet until I pressed ESC. I fiddled with the speed and found one I believe will work.

However, is it possible to have the macro start at cell A1, scroll down rows until it hits a certain cell, such as A70, scroll back up rows to cell A1, and then continuously repeat the cycle until I press ESC.

Thanks again for your time.

minion

 

[Kenneth Hobson]

Yes. Set LastRow=70.

 

[minion]

Kenneth,

Thank you sooo much! I appreciate your time and effort spent on solving this little problem. The macro works like a charm! You have helped me tremendously!

One last question about the macro...(this is not a critical item) After I press the form button and the macro starts scrolling down the screen, it randomly stops at around rows A27-A30 and the computer attempts to open another version of the same spreadsheet. This of course stalls the computer, so I press ESC and press the form button again to have the macro continue down the screen with no further problems. This is not a big deal and does not always happen. I just wanted to let you know. Thanks again.

minion

 

[OLeflat]

This Macro is exactly what I need, however, it is crashing my excel after about 5 minutes of scrolling. Does anybody know why it would be doing this? Also, I want the continuous scrolling to be used in a shared workbook, and I want it to update automatically as people update it (it is being used for a checklist). Does anyone know how I can change the coding a bit so the workbook will auto refresh every 5 minutes or so while its still scrolling? I don't mind if it stops scrolling for a bit to update as long as it resumes the scrolling.

 

[OLeflat]

Sorry...Forgot to mention I am running Excel 2010 ^