Hi Catalin
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
Here is the code I tested in ThisWorkbook VBA module:
Call startClock
Call runspeech
End Sub
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 & """'"
Next Cell
Debug.Print "done"
End If
End If
End Sub
Sub SpeechStart(ByVal text As String)
Application.Speech.Speak text
End Sub
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.
