Application.Ontime

[ccordner]

Hi

I'm using the application.ontime method to run a macro at various times during the day (every time a location opens or closes). There may be several locations all opening at the same time and some of them may already have opened when the workbook is opened.

The opening and closing times are listed chronologically in the sheet "Time Events". This code is designed to run the macro at the first time listed, or immediately if that is in the past. It works fine:

 Private Sub Workbook_Open()

RowNumber = 2

If Sheets("Time Events").Range("C2") <= TimeValue(Now) Then Application.OnTime Now + TimeValue("00:00:01"), "RemindMe" Else Application.OnTime Sheets("Time Events").Range("C2"), "RemindMe"

End Sub

So, using the logic, I wanted the code to run the macro at each subsequent time, or immediately if the times are in the past or the same.

Public dtime As Date
Public RowNumber As Long

Sub RemindMe()

MsgBox (Sheets("Time Events").Cells(RowNumber, 1) & Chr(10) & Sheets("Time Events").Cells(RowNumber, 2) & Chr(10) & Format(Sheets("Time Events").Cells(RowNumber, 3), "hh:mm"))
RowNumber = RowNumber + 1


If Sheets("Time Events").Cells(RowNumber, 3) > "" Then
    dtime = Sheets("Time Events").Cells(RowNumber, 3)
    If dtime > Now Then Application.OnTime dtime, "RemindMe" Else Application.OnTime Now + TimeValue("00:00:01"), "RemindMe"

End If

End Sub

This works, as long as all the times are in the future. However, if one time is in the past, it just runs the macro immediately, even if some of the times are in the future.

Any ideas why?

Chris

 

[ccordner]

Solved it! I should have been using Timevalue(Now).

Chris