DoEvents, Waiting, and Editing

Question

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

Solution 1

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.

Solution 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 start mySub() it will run indefinately. Between 3 AM and 7 AM it will run Aespire_Production_Board on every loop. It also allows the user to interact. The code can be put into Break mode using CTRL-Break.

Comments

[user2263642]

is there such as thing as Goto.OnTime Now + TimeValue("00:00:5"), "test2"

[Doug Glancy]

@user2263642, You can Goto a label in the same subroutine, and have an OnTime statement after the label. You could also have it in one branch of an If statement. What are you trying to accomplish?

[user2263642]

At a certain time interval, I want the macro to start over and run again. See posting below. I want the macro to start running at 5 or 7 AM depending on a variable, shut off at 3 AM, and update as often as possible in between.