.ontime

[Grrrrrumpy]

I need to display 30 rows of an Excel 2003 worksheet, pause, page down,
pause...etc until I reach the bottom, the go to the top and start all over
again.
I was given a .WAIT application that works. I created a command button,
right-clicked on it, then clicked on "View Code", pasted the code in there
and it worked
fine. It needs to run for long periods of time and I was warned I could run
out of stack space. My system does crash if I run it a long time.
I was also given code for an ".ONTIME" application. I tried pasting this
under the "View Code" of the "Command Button". This does not work. I'm not
familiar with the Excel environment. I guess I need simple instructions on
where to put this code. I do want to activate the code with a command button.
The code is listed below. Thanks in advance.

This version is a bit more stable. As you cannot pass parameters in a call
to .OnTime, use public variables to pass settings:
SHIFT+ESC to halt execution.

'<Standard Module code>
Public StartRange As Range
Public NextRange As Range

Public Const HOURS_DELAY As Long = 0
Public Const MINUTES_DELAY As Long = 0
Public Const SECONDS_DELAY As Long = 10

Public Const ROWS_JUMP As Long = 30
Public Const COLUMNS_JUMP As Long = 0

Public mStopLoop As Boolean

Public Sub MyGoToLoop()

With Application
..Goto NextRange, True

If ActiveCell.Row + ROWS_JUMP < ActiveSheet.UsedRange.Rows.Count Then
Set NextRange = ActiveCell.Offset(ROWS_JUMP, COLUMNS_JUMP)
Else
Set NextRange = StartRange
End If

If mStopLoop = False Then
..OnTime Now + TimeSerial(HOURS_DELAY, MINUTES_DELAY, SECONDS_DELAY),
"MyGoToLoop"
Else
..Goto StartRange, True
..OnKey "+{ESC}", ""
End If
End With

End Sub

Public Function StopLoop()
mStopLoop = True
End Function
'</Standard Module code>

'<WS Copde>
Private Sub CommandButton1_Click()

Set StartRange = Range("A1")
Set NextRange = Range("A1").Offset(ROWS_JUMP, COLUMNS_JUMP)
mStopLoop = False

With Application
..Wait Now + TimeSerial(HOURS_DELAY, MINUTES_DELAY, SECONDS_DELAY)
..OnKey "+{ESC}", "StopLoop"
End With

Call MyGoToLoop

End Sub
'</WS Copde>

 

[Jim Cone]

G,
I have not reviewed the code you posted. However it does show
that the top section goes in a Standard Module and the lower section
goes in the Worksheet module. ( noted with <<< )

The worksheet module is accessed by right-clicking the sheet tab
and selecting "View Code".
Once there - on the toolbar - Insert | Module provides a Standard module
that the rest of the code goes in.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Grrrrrumpy" <[email protected]>
wrote in message
I need to display 30 rows of an Excel 2003 worksheet, pause, page down,
pause...etc until I reach the bottom, the go to the top and start all over
again.
I was given a .WAIT application that works. I created a command button,
right-clicked on it, then clicked on "View Code", pasted the code in there
and it worked
fine. It needs to run for long periods of time and I was warned I could run
out of stack space. My system does crash if I run it a long time.
I was also given code for an ".ONTIME" application. I tried pasting this
under the "View Code" of the "Command Button". This does not work. I'm not
familiar with the Excel environment. I guess I need simple instructions on
where to put this code. I do want to activate the code with a command button.
The code is listed below. Thanks in advance.

This version is a bit more stable. As you cannot pass parameters in a call
to .OnTime, use public variables to pass settings:
SHIFT+ESC to halt execution.

'<Standard Module code> '<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Public StartRange As Range
Public NextRange As Range

Public Const HOURS_DELAY As Long = 0
Public Const MINUTES_DELAY As Long = 0
Public Const SECONDS_DELAY As Long = 10

Public Const ROWS_JUMP As Long = 30
Public Const COLUMNS_JUMP As Long = 0

Public mStopLoop As Boolean

Public Sub MyGoToLoop()

With Application
..Goto NextRange, True

If ActiveCell.Row + ROWS_JUMP < ActiveSheet.UsedRange.Rows.Count Then
Set NextRange = ActiveCell.Offset(ROWS_JUMP, COLUMNS_JUMP)
Else
Set NextRange = StartRange
End If

If mStopLoop = False Then
..OnTime Now + TimeSerial(HOURS_DELAY, MINUTES_DELAY, SECONDS_DELAY),
"MyGoToLoop"
Else
..Goto StartRange, True
..OnKey "+{ESC}", ""
End If
End With

End Sub

Public Function StopLoop()
mStopLoop = True
End Function
'</Standard Module code>

'<WS Copde> '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub CommandButton1_Click()

Set StartRange = Range("A1")
Set NextRange = Range("A1").Offset(ROWS_JUMP, COLUMNS_JUMP)
mStopLoop = False

With Application
..Wait Now + TimeSerial(HOURS_DELAY, MINUTES_DELAY, SECONDS_DELAY)
..OnKey "+{ESC}", "StopLoop"
End With

Call MyGoToLoop

End Sub
'</WS Copde>

 

[Grrrrrumpy]

Thank you sir. Now it runs and works great. You have bailed me out of a dire
situation. Again, thank you so much.