Sound alarm when condition met

[tonydev314]

So I've a number of cells in my SS that I enter a future time into, or some code does, and currently when that time is reached I use conditional formatting to set the cell fill colour.

An aside here is that sometimes the cell doesn't update until i change another cell somewhere on the SS - even if it's just clicking in it and hitting Enter. This means sometimes I switch the SS to the foreground and the cells are not highlighted until I actually DO something with the SS.


What I'd like to do is in addition to the formatting, have an alert sound. I don't think there's an event for "Conditional Format Met" that I can hook into? Nor does it appear that the Selection Changed event is fired in this case.


What I'm considering is writing a timer that runs when the Workbook is opened, that goes through the cells of concern and registers the earliest future time with a timer. I then update this time if necessary whenever one of the values in the effected cells is changed, or when the timer fires, such that the timer is always set to run on the soonest future time in any of these cells.

Does this seem like a reasonable solution, or can anyone suggest anything better?

 

[Dan_W]

I think that you're right that there is no "Conditional Format Met" event, but there is no reason that can't just replicate the logic into VBA. As you say, it's a matter finding the right event.
According to your explanation, either you're entering the code manually or you have some subroutine/function that does it. That being the case, it seems that either time is when you want to be checking to see whether or not the conditions are met.

So for when you're entering data manually, amongst the Worksheet events, what about the Change event? And for when you have code changing the contents of the cells, you could run the check then. Then for the alarm sound, you could either use a system sound (like Beep) or play a wave file.

You could use a timer, but it seem a bit unnecessary (and arguably slower) as either of the above events would be the earliest it could possibly trigger.

 

[tonydev314]

I think that you're right that there is no "Conditional Format Met" event, but there is no reason that can't just replicate the logic into VBA. As you say, it's a matter finding the right event.
According to your explanation, either you're entering the code manually or you have some subroutine/function that does it. That being the case, it seems that either time is when you want to be checking to see whether or not the conditions are met.

So for when you're entering data manually, amongst the Worksheet events, what about the Change event? And for when you have code changing the contents of the cells, you could run the check then. Then for the alarm sound, you could either use a system sound (like Beep) or play a wave file.

You could use a timer, but it seem a bit unnecessary (and arguably slower) as either of the above events would be the earliest it could possibly trigger.

Ah you slightly mistake me.
There are two use cases:
1) I enter a value, e.g. 8h in a cell. The selection_Changed event picks up and sets the time in the cell to 8h in the future.
2) I click a button on a form that sets the time in the cell to x hours in the future.

Since code is involved in both of these I can obviously do something else as well as update the value, but the alarm needs to go off 8h in the future (in this case).

 

[Logit]

Option Explicit

Dim SchedRecalc As Date
Private Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Sub PlayWAV()
Dim WAVFile As String
    WAVFile = "Alarm01.wav"
    WAVFile = "C:\Users\My\Desktop" & " \ " & WAVFile
    Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Sub Recalc()
Call SetTime
End Sub

Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime ThisWorkbook.Sheets(1).Range("A1").Value, "PlayWAV"     '<<---- set alarm time here'
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

 

[tonydev314]

Wow!
Thank you for that - incredible! I was just hoping for some general guidance not the full solution! You've saved me a lot of time hunting down code on how to set timers and play sounds!
I'll have a play with it and see how I get on