Why won't Application.Ontime work for me?.....

[timsp]

I have a spreadsheet with a macro that dynamically records new lines of data every time a stock price changes. It works fine, except that it starts as soon as I open the workbook. I always open the workbook about an hour before the stock market opens, and I would like the macro to start at 9:30 EST.

I tried using Application.Ontime, but my workbook still starts writing new lines of data as soon as I open it. Any help is appreciated.

This is my code:

Private Sub Workbook_Open()
'Runs a macro at 3:30 PM


Application.OnTime TimeValue("15:30:00"), "Worksheet_Calculate"


End Sub








Private Sub Worksheet_Calculate()



nextrow = Range("G65536").End(xlUp).Row + 1
If Cells(nextrow - 1, 7) <> Range("d4") _
And Abs(Cells(nextrow - 1, 7) - Range("d4")) >= 0.02 _
And Int(Range("d4") / 0.02) = Range("d4") / 0.02 Then
Application.EnableEvents = False
Cells(nextrow, 7) = Range("d4")
Cells(nextrow, 8) = Application.WorksheetFunction.Round(Range("d5"), 3)
' Cells(nextrow, 9) = Right(Now, 11)
Cells(nextrow, 9) = Now()
Cells(nextrow, 10) = Range("d3")
Cells(8, 4) = Range("d4")
Cells(9, 4) = Application.WorksheetFunction.Round(Range("d5"), 3)
Application.EnableEvents = True
End If

' checks for ratio change V and if true adds line
nextrow = Range("G65536").End(xlUp).Row + 1
If Abs((Cells(nextrow - 1, 8)) - Range("d5")) >= 0.001 And _
(Cells(nextrow - 1, 7) - Range("d4")) = 0 Then
Application.EnableEvents = False
Cells(nextrow, 7) = Range("d4")
Cells(nextrow, 8) = Application.WorksheetFunction.Round(Range("d5"), 3)
' Cells(nextrow, 9) = Right(Now, 11)
Cells(nextrow, 9) = Now()
Cells(nextrow, 10) = Range("d3")
Cells(8, 4) = Range("d4")
Cells(9, 4) = Application.WorksheetFunction.Round(Range("d5"), 3)
Application.EnableEvents = True
End If

End Sub

 

[John_w]

The Worksheet_Calculate is adding the new rows, therefore try this with your existing code placed where indicated. There should be no need for Workbook_Open nor Application.OnTime.

Private Sub Worksheet_Calculate()
    If Now >= TimeValue("09:30:00") Then
        'PUT YOUR CODE HERE
    End If
End Sub

PS please use CODE tags.

 

[timsp]

The Worksheet_Calculate is adding the new rows, therefore try this with your existing code placed where indicated. There should be no need for Workbook_Open nor Application.OnTime.

Private Sub Worksheet_Calculate()
    If Now >= TimeValue("09:30:00") Then
        'PUT YOUR CODE HERE
    End If
End Sub

PS please use CODE tags.

Thank you very much, John. I will do as suggested and run it tomorrow morning.

 

[joeu2004]

    If Now >= TimeValue("09:30:00") Then
        'PUT YOUR CODE HERE
    End If

Now() is effectively Date+Time. So that condition will always be true. Instead, try:

If Time >= TimeValue("09:30:00") Then
    '.... your code ....
End if

PS: timsp specifically wrote "9:30 EST" [sic; I suspect he means ET, not EST] and offered an example that starts at "3:30 PM" local time. So perhaps that should be TimeValue("15:30:00").

 

[John_w]

Well spotted, joeu2004; use Time instead of Now.

 

[timsp]

Well spotted, joeu2004; use Time instead of Now.

Thanks guys, it worked perfectly.