Log In
Register
Home
New Member
December 14, 2020
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
