VBA automatically scroll down one row at a time ... using Wait function ?

[palaeontology]

I have a sheet with the top three rows frozen.

I have a command button (CommandButton2) which, when pressed, should start the sheet automatically scrolling downwards just one row every 3 seconds. So because the top three rows are frozen, I'd like row 4 to scroll up out of sight after 3 seconds, then row 5 to scroll up out of sight after a further 3 seconds, etc etc etc.

After the sheet has scrolled to row 40, I'd like the automatic scrolling to stop

This is what I've tried so far, but it is not working at all ...

Private Sub CommandButton2_Click()
    For i = 4 To 40
    Range("a4").Select
    ActiveWindow.SmallScroll Down:=i
    Application.Wait (Now + TimeValue("00:00:03"))
    Next
End Sub

My vba is extremely poor, so it has taken me some time to get even to this stage. I apologise if it is embarrassingly incorrect.

Kindest regards,

Chris

 

[DanteAmor]

Try this:

Private Sub CommandButton2_Click()
  Dim i As Long
 
  Range("A4").Select                                'Outside the sentence For
 
  For i = 1 To 40
    Application.Wait (Now + TimeValue("00:00:03"))
    ActiveWindow.SmallScroll Down:=1                'must be 1
  Next
End Sub

If you want the visible cell in column A to remain selected, then:

Private Sub CommandButton2_Click()
  Dim i As Long
   
  Range("A4").Select

  For i = 1 To 40
    Application.Wait (Now + TimeValue("00:00:03"))
    ActiveWindow.SmallScroll Down:=1                'must be 1
    Range("A" & i + 3).Select
  Next
End Sub

----- --

 

[palaeontology]

Dante, thank you so much for putting that together.

Not only have you solved my problem, but my VBA will be all the better for it.

This was my first attempt at using 'i'.

Thankyou, again,

kindest regards,

Chris