Sound Alert on dynamic cell value

[excelakos]

Hi there. I need to get a sound alert (sound i want in wav. format) when value in cell meets the criteria. Notice value is dynamically changing.

Details:

In cell E1 i have entered the =NOW() function. I use a couple of codes to make this change every minute.

First code in the sheet itself is:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"
End Sub

and second code in a module is:

Sub UpDateClock()
' *** Change Sheet name and Range reference to suit ***
Worksheets("Sheet1").Range("E1").Calculate
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"
End Sub

Then in cell range C4:C34 i have values in format of date/month/year time/min like 2/4/2017 14:30
Then in cell range B4:B34 i have =C4-$E$1
So the values in range B4:B34 are dynamically changing each time $E$1 changes (which is set per minute)

All i need is to get 2 different sound alerts at the exact time when each criteria is met once.
First alert when value in range B4:B34 gets < 8 minutes which i understand to equals to 8/(24*60)=0.0056 in number format. But i need to get the alert played only once.

Second alert when value in range B4:B34 gets < 6 minutes which i understand to equals to 6/(24*60)=0.0042 in number format. But i need to get the alert played only once.

Important notice that there could be multiple cells in range B4:B34 with the exact same values.

Is there any vba code to manage this scenario?

 

[mole999]

Maybe this post could help

https://www.mrexcel.com/forum/excel-questions/991903-sound-message-box.html

Jaafa has posted others here somewhere and i'm convinced there was discussion on embedding wav into excel to make it always available