I’ve built a dynamic to-do scheduler that allows the user to input tasks alongside their respective start times.

The clock runs in real time, and conditional formatting is used to highlight the current task in green. When the clock gets to n number of seconds (according to WarningSeconds) before the start time of the next task, the current task turns red, and the Beep sound plays five times.

Then, when the next task kicks in, it turns green and the previous one gets crossed out. That’s great, but I want the Beep to play five times at the start of the new task as well. I’ve tried various things, but none of my implementations work.

What can I add to my code to get this working?

Check out the example here: https://postimg.cc/xkpnPH4R.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Public IsRunning As Boolean
Public AlarmCounter As Integer

Sub StartStopClock()
        IsRunning = Not IsRunning
        AlarmCounter = 0 ' Initialise the counter
        ' Start the timer
        TimerLoop
End Sub

Sub TimerLoop()
    On Error Resume Next
    Do While IsRunning
        DoEvents
        Sheet1.Range("Clock").Value = TimeValue(Now)
        If AlarmCounter < 5 Then
            ActivateAlarm
        End If
    Loop
End Sub

Sub ActivateAlarm()
    If Range("WarningAlert").Value = True Then
        Beep
        Sleep (1000)
        AlarmCounter = AlarmCounter + 1 ' Increment the counter
    End If
End Sub

The formula for WarningAlert is:

=AND(Clock>=ROUND(MINIFS(tblTask[Time],tblTask[Time],">"&Clock)-TIME(0,0,WarningSeconds),10),Clock<MAX(tblTask[Time]))