DoEvents, Waiting, and Editing

doevents, user-controls, vba, wait

Solution

Instead of `Wait`, try `OnTime`. To demonstrate, paste this in a normal module and run `Test`. Range A1 of the active sheet will increment every five seconds and you'll be able to work in between. It also works if you are in Edit mode when the five seconds elapses:

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

Note that the `OnTime` statement at the end of the sub calls the sub again recursively. Here's some more info.

Problem

I have a set of code that contains: ``` Application.Wait (Now + TimeValue("4:00:00")) ``` This is essentially pausing the macro for a four hour window from 3 AM (when it finishs running the code) till 7 AM (when it should resume). The code is on an endless loop essentially. I want the user to be able to have control during that time to edit certain cells. I have tried ``` DoEvents ``` but have not found the way to keep the macro running, yet provide control to the user during that time when the macro is doing nothing but waiting. Any insight would be appreciated. Thanks! EDIT: One more followup question. I created this macro to reference the actual macro "Production_Board". I want this macro to run all the time and refresh as often as possible. By using the goto startagain, it tries to start to launch the macro again before the macro has even started due to the "ontime" delay interval. How could I make the sub RunMacro start again the second that the macro "Production_Board" finishes? ``` 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 ```

Original source