December 14, 2020
Please I have a problem with my macro. I will like the macro to activate at 6 – Production start, 8 – break time, 8.10 – break is now over, 10 – break time, 10.10 – break is now over, 12 – Lunch time, 12.20 – Lunch is now over, 14 – break time, 14.10 – break is now over
November 8, 2013
Here is the code I tested in ThisWorkbook VBA module:
The code for runspeech: (note that you can pass parameters as well to the called procedure, this helps simplifying the code)
If clockOn = True Then
Dim Rng As Range, Cell As Range
If Weekday(Now(), vbMonday) < 5 Then Set Rng = Range("H52:H53")
If Weekday(Now(), vbMonday) = 5 Then Set Rng = Range("H66:H74")
If Not Rng Is Nothing Then 'will be nothing for weekdays 6,7
For Each Cell In Rng.Cells
Application.OnTime TimeValue(Cell.text), "'SpeechStart """ & Cell.Offset(0, 1).text & """'"
Sub SpeechStart(ByVal text As String)
Keep in mind that you have runspeech called at workbook open event. This means that if you open the file multiple times during the day, the code will run and set ontime procedures, at those times you will hear the messages more than once.
Maybe it's best to run the code from a button instead of workbook open to avoid duplicate messages.