Loop

[Cagey93]

Hello,

I am looking for a bit of code to add into the below but I am struggling.

What it needs to do is say if the cell A1 is = to cell B4 then it needs to speak as per the below code. However this needs to loop constantly so everyday at the the shown in B4 it speaks aloud.

I have it working in principle just not looping or from the matching cells.

Sub SetTime()

SchedRecalc = Now + TimeValue("00:00:02")

Application.OnTime SchedRecalc, "Recalc"

Sheet1.Range("A26").Value = OK

Application.Speech.Speak ("Make Your Selection. Then press the enter button.")



End Sub

 

[Cagey93]

Hello,

I am looking for a bit of code to add into the below but I am struggling.

What it needs to do is say if the cell A1 is = to cell B4 then it needs to speak as per the below code. However this needs to loop constantly so everyday at the the shown in B4 it speaks aloud.

I have it working in principle just not looping or from the matching cells.

Sub SetTime()

SchedRecalc = Now + TimeValue("00:00:02")

Application.OnTime SchedRecalc, "Recalc"

Sheet1.Range("A26").Value = OK

Application.Speech.Speak ("Make Your Selection. Then press the enter button.")



End Sub

Sorry this missed a few bits.

I currently have a clock which is constantly running but during the day we have 5 times where we need excel to speak just at those given times. So for example 1:45, 2:45 and so on it would need to say the above message. The running clock needs the "Recalc" but we cant seem to get another macro to constantly run. Is there a way to incorporate this into one big macro or run 2 side by side?

 

[Micron]

Maybe if you put this into Workbook module

Private Sub workbook_Open()
alertTime = #2:49:00 PM# + TimeValue("00:02:00") 'this adds 2 min. to time workbook opens for first run. Adjust accordingly.
Application.OnTime alertTime, "EventMacro"

In a standard module right under Option Explicit (you do use that option, I hope)

Public alertTime As Variant

Public Sub EventTimer()
    alertTime = alertTime + TimeValue("00:02:00") 'this is a 2 min. interval. Adjust accordingly.
    Application.OnTime alertTime, "EventTimer"
    MsgBox "timer ran"

I assumed by 1:45 you meant PM so I showed with a PM time example. Change to AM if it's in the morning.

 

[Cagey93]

Maybe if you put this into Workbook module

Private Sub workbook_Open()
alertTime = #2:49:00 PM# + TimeValue("00:02:00") 'this adds 2 min. to time workbook opens for first run. Adjust accordingly.
Application.OnTime alertTime, "EventMacro"

In a standard module right under Option Explicit (you do use that option, I hope)

Public alertTime As Variant

Public Sub EventTimer()
    alertTime = alertTime + TimeValue("00:02:00") 'this is a 2 min. interval. Adjust accordingly.
    Application.OnTime alertTime, "EventTimer"
    MsgBox "timer ran"

I assumed by 1:45 you meant PM so I showed with a PM time example. Change to AM if it's in the morning.

Thank you, I will give this a try.

 

[Cagey93]

Maybe if you put this into Workbook module

Private Sub workbook_Open()
alertTime = #2:49:00 PM# + TimeValue("00:02:00") 'this adds 2 min. to time workbook opens for first run. Adjust accordingly.
Application.OnTime alertTime, "EventMacro"

In a standard module right under Option Explicit (you do use that option, I hope)

Public alertTime As Variant

Public Sub EventTimer()
    alertTime = alertTime + TimeValue("00:02:00") 'this is a 2 min. interval. Adjust accordingly.
    Application.OnTime alertTime, "EventTimer"
    MsgBox "timer ran"

I assumed by 1:45 you meant PM so I showed with a PM time example. Change to AM if it's in the morning.

Hello, sorry struggling with this code but I may not have been very clear.

the aim is to have a running clock with the seconds showing but during the day at certain times (5 times a day) it needs to speak aloud at those times to remind people. This is to be used on a TV screen running all day. Im hoping to have those times in cells for the macro to read and only alert at those times.

The issue I feel is the running clock may impact the speaking element as you don't seem to be able to have 2 macros running side by side. Is there a way to get these running together or in one macro that is constantly running.

 

[Micron]

You made no mention of a visible clock so if you're wanting someone to build that and the code for you then sorry, that's not me. You have your speech portion of code, and if that's working for you, all you should need to do is add it to what I gave you.

Opening the workbook will cause that code to run every x minutes after a certain clock time has been reached, which is what you asked for. If you want to get those times from cells, modify the lines containing the time by referencing the sheet and ranges for those times.