Automate Scrolling in Excel using VBA

[MarkoX50]

Hi,

I am using 2010 excel and have a data connection that refreshes every 3 minutes. I need to help to create a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> macro that automatically scrolls my data from row 5 to row 50 and loop back again. The problem is that the macro i am using stops the data connection or freezes.

My data connection is fed from a website every 3 minutes updating my Dashboard report for the company to see. I have 50 rows displaying but need it to scroll up and down continuously and also refresh the data every 3 minutes.

I am not an expert and found the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code for scrolling which is:

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

How can i get my report to scroll up and down and still refresh the data every 3 minutes.

Thanks

 

[AlphaFrog]

Insert the DoEvents function within your loop. It yields execution so that the operating system can process other events.

[color=darkblue]Private[/color] [color=darkblue]Declare[/color] [color=darkblue]Sub[/color] Sleep [color=darkblue]Lib[/color] "kernel32" ([color=darkblue]ByVal[/color] dwMilliseconds [color=darkblue]As[/color] [color=darkblue]Long[/color])


[color=darkblue]Sub[/color] SlowScroll()
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    Range("A5").Select
    [color=darkblue]Do[/color]
[COLOR=#ff0000]        DoEvents[/COLOR]
        Sleep 20   [color=green]'Pause 20ms - Scroll speed adjust[/color]
        ActiveWindow.SmallScroll Down:=1
        r = r + 1
        [color=darkblue]If[/color] r = 300 [color=darkblue]Then[/color] Range("A5").Select
    [color=darkblue]Loop[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MarkoX50]

Hi, that does not work. All that happens is a processing function which freezes and the debug error comes up. the "r = r + 1" is in yellow.

my sheet is frozen on row 4. Row 5 to 50 needs to move up and down.

Please help.

 

[AlphaFrog]

Sorry. Forgot to reset r

Note: the Private Declare Sub Sleep... has to go at the very top of the code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Declare[/COLOR] [COLOR=darkblue]Sub[/COLOR] Sleep [COLOR=darkblue]Lib[/COLOR] "kernel32" ([COLOR=darkblue]ByVal[/COLOR] dwMilliseconds [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
 
[COLOR=darkblue]Sub[/COLOR] SlowScroll()
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Range("A5").Select
    [COLOR=darkblue]Do[/COLOR]
        DoEvents
        Sleep 20   [COLOR=green]'Pause 20ms - Scroll speed adjust[/COLOR]
        ActiveWindow.SmallScroll Down:=1
        r = r + 1
        [COLOR=darkblue]If[/COLOR] r = 50 [COLOR=darkblue]Then[/COLOR] Range("A5").Select[COLOR=#ff0000]: r = 0[/COLOR]
    [COLOR=darkblue]Loop[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[MarkoX50]

Thanks. I got the scroller to work but the data does not refresh. It show as "running background query" and stays like that until i have to escape the function.

Also, how to i pause the scroller in that code.

 

[MarkoX50]

Please help me with this...I managed to get the Scrolling working perfectly, however, i have the following 2 issues:

1. How do i get the Macro to pause, refresh the data and then start again. I have a query with pulls data from a website every 5 minutes. I need the macro to pause while the data refreshes or refresh non the less. This is important as my data is fed from a website to my excel and streamed onto multiple monitors. So Refreshing the Data and Scrolling need to work hand in hand. Please help on this?

2. How to i stop the Macro using a command button?

Much appreciated...

 

[Pratik2058]

Can I put auto-refresh External data script in above? please help me out with that.