Stop a NextTick App

[someguy33]

Excel 2003:

First of all I love the board and I have never had to ask a question because I can usually get the answers scanning through all the knowledge stored, but I know I am doing something wrong and I can't get the nexttick function to stop as it should be. Everything else I have figured out and I will be 100% done with my project once i can the darn thing to stop. Can someone look through the code below and help me. Thanks in advance.

Option Explicit
Public NextTick As Date
Sub Pricenet()
Dim Form As Object
Dim Rng As Range
Dim Rng2 As Range
Dim NextTick As Date
Set Rng = Worksheets("PriceNet").Range("A15")
Set Rng2 = Worksheets("PriceNet").Range("D6")
ThisWorkbook.Sheets(1).Range("A20") = "Notifications Are Now Active"

If Rng2 <> 0 Then
ThisWorkbook.Sheets(1).Range("C2") = Time
ThisWorkbook.Sheets(1).Range("A1") = "Please Implement Your Price Changes!!"
Beep
Run "TesttheBeep"
Application.Speech.Speak _
("You have an updated fuel price on price net waiting for implementation")
Run ("TesttheBeep")
NextTick = Now + TimeValue("00:00:30")
Application.OnTime NextTick, "PC"
Exit Sub
End If
If Rng2 = 0 Then
ThisWorkbook.Sheets(1).Range("A1") = "There are no changes to Pricenet"
ThisWorkbook.Sheets(1).Range("C2") = Time
NextTick = Now + TimeValue("00:00:10")
Application.OnTime NextTick, "PriceNet"
End If
Exit Sub


End Sub
Sub StopClock()
On Error Resume Next
Application.OnTime NextTick, "PriceNet", , False
End Sub

 

[JoeMo]

You have to decide under what conditions you want to halt the Ontime method and then place a call to the procedure StopClock in your procedure PriceNet if those conditions are met.

Here's an example:

Dim dTime As Date
Sub myMacro()
Static ctr As Integer
ctr = 0
dTime = Now + TimeSerial(0, 0, 5)
Do Until ctr > 2
    Application.OnTime dTime, "mymacro"
    MsgBox "Hello " & ctr + 1
    ctr = ctr + 1
Loop
Call stopit

End Sub
Sub stopit()
On Error Resume Next
Application.OnTime dTime, "mymacro", , False
If Err.Number <> 0 Then MsgBox "ERROR"
End Sub

 

[someguy33]

Excellent. Your post helped me relieze there is 1000 solutions to the problem but I was only looking at the one. Thanks.