Application.Ontime

ccordner

Active Member
Joined
Apr 28, 2010
Messages
344
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:

Code:
 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.

Code:
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top