DoEvents, pending and editing

Question

I have a set of code that contains:

Application.Wait (Now + TimeValue("4:00:00")) 

This essentially pauses the macro for a four-hour window from 3 AM (when it finishes executing the code) to 7 AM (when it should resume). The code is on an infinite loop.

I want the user to have control during this time to edit certain cells. I tried

 DoEvents 

but they didn’t find a way to keep the macro working, but at the same time provide control over the user during this time when the macro does nothing but wait.

Any insight would be appreciated. Thanks!

EDIT:

Another interrogative question. I created this macro to reference the actual Production_Board macro. I want this macro to work all the time and be updated as often as possible. Using startoain goto, he tries to start running the macro before the macro even starts because of the "ontime" delay interval.

How can I get sub RunMacro to start a second one that ends with the Production_Board macro?

 Sub RunMacro startagain: Dim hour As Integer Dim OT As String hour = 0 OT = "Empty" hour = Sheets("Calculations").Range("DR1").Value OT = Sheets("Black").Range("D4").Value If OT = "Y" Then If hour = 3 Or hour = 4 Then Application.OnTime TimeValue("05:00:00"), "Aespire_Production_Board" Else Application.OnTime Now + TimeValue("00:00:30"), "Aespire_Production_Board" End If Else If hour = 3 Or hour = 4 Or hour = 5 Or hour = 6 Then Application.OnTime TimeValue("07:00:00"), "Aespire_Production_Board" Else Application.OnTime Now + TimeValue("00:00:30"), "Aespire_Production_Board" End If DoEvents GoTo startagain 

Answer 1

Instead of Wait try OnTime . To demonstrate, insert this into a regular module and run Test . The A1 range of the active sheet will increase every five seconds, and you can work between them. It also works if you are in edit mode when five seconds have passed:

 Sub test() test2 End Sub Sub test2() ActiveSheet.Cells(1, 1).Value = ActiveSheet.Cells(1, 1).Value + 1 Application.OnTime Now + TimeValue("00:00:5"), "test2" End Sub 

Notice that the OnTime statement at the end of sub causes recursion. See below for more information.

Answer 2

 Sub mySub() Do If Time() >= #3:00:00 AM# And Time() <= #7:00:00 AM# Then Aespire_Production_Board End If DoEvents Loop End Sub 

Once you run mySub (), it will work indefinitely. Between 3:00 and 7:00 he will run Aespire_Production_Board in each cycle. It also allows the user to interact. Code can be put into break mode using CTRL-Break.