Audio alarm/alert

mpfx · Aug 2, 2002

Hi all,
Is it possible to have a wav. file play a sound when a value returns a true answer?
I have live data and have an alert that returns a "New High" or "New Low" value but would like a sound to play as well.

TIA

Legacy 98055 · May 7, 2003

Patrick.

Here is an easier version to do this using a user defined function. Place this function in some cell. The arguments are:

1. Any expression which can be evaluated as TRUE or FALSE. This would include any Excel function of a formula of various functions and values which you could evaluate as either true or false.

2. The path of the sound file you want to play whenever the Expression evaluates to TRUE.

3. The stop range. Any range that you will select to end the alarm.

Example:

=sta(MAX(B:B)>20, "C:\WINNT\MEDIA\TADA.WAV",A:A)

The function will evaluate to TRUE and sound the alarm when any value in column B is greater than 20.

"C:\WINNT\MEDIA\TADA.WAV" is the wav file which will play in a continuous loop until...

You select a cell within the stop range which is Column A (A:A = the last argument). Change the file path argument to suit.

An immediately apparent flaw would be if you happen to have column A selected. The sound will not play. So make sure you don't have the stop range selected.

There are a hundred other ways to stop the wave file but this is just to give you some ideas.

This will only work with a single STA function as only one timer will be allowed to run at any given time. You could change this by adding an argument which would take a "priority" value, so to speak, and then cache your calculations. You could also use the WorkSheet_Change event but I thought this to be a bit more flexible and easier to work with.

Copy all of the code into a standard module. Use the function. Download the example below if you have any problems.

<pre>Option Explicit

Private Declare Function PlaySound _

Lib "winmm.dll" _

Alias "PlaySoundA" ( _

ByVal lpszName As String, _

ByVal hModule As Long, _

ByVal dwFlags As Long _

) As Long

Private Declare Function SetTimer _

Lib "user32" ( _

ByVal hwnd As Long, _

ByVal nIDEvent As Long, _

ByVal uElapse As Long, _

ByVal lpTimerFunc As Long _

) As Long

Private Declare Function KillTimer _

Lib "user32" ( _

ByVal hwnd As Long, _

ByVal nIDEvent As Long _

) As Long

Private lngTimerID As Long

Private rngKillWav As Range

<hr size=1 style="verticalAlign=top;">

Public Function STA( _

Expression As Boolean, _

Wav_Path As String, _

Wav_Kill As Range _

) As String

 'do not edit and use a DoEvents/DoLoop to wait for a selection change.

 'you will need a reboot. The timer is more stable

If Expression Then PlaySound Wav_Path, ByVal 0&, &H20000 Or &H1 Or &H8

 'change "STA" to whatever or nothing at all.

STA = "STA"

 'this is the module level range which will interupt the sound loop

Set rngKillWav = Wav_Kill

 'this is only set up to work with one single function. You would need to

 'cache any other calculations and run them after each timer process has ended

If lngTimerID = 0 Then

lngTimerID = SetTimer(0, 0, 10, AddressOf RunProc)

End If

End Function

<hr size=1 style="verticalAlign=top;">

Private Sub RunProc(ByVal hwnd As Long, _

ByVal uint1 As Long, _

ByVal nEventId As Long, _

ByVal dwParam As Long)

On Error Resume Next

 'if the correct range is selected, the sound loop and timer will end.

If Not Intersect(Selection, rngKillWav) Is Nothing Then

PlaySound vbNullString, 0, 0

Set rngKillWav = Nothing

KillTimer 0, lngTimerID

lngTimerID = 0

End If

End Sub <hr size=1 style="verticalAlign=top;">

</pre>

Google Search<text> - </text>Email TsTom<text> - </text>Where do I put this code?<text> - </text>Copy Code to your Clipboard<text> - </text>Download Example

<pre> If you need further information pertaining to the above code, click on the term in question.

Many, but not all, are links to MSDN. If the downloaded file does not behave correctly in your

browser, then right click the link, choose "Save target as" and download to your hard-drive.

Tom</pre>

Audio alarm/alert

mpfx

Board Regular

Legacy 98055

Guest

Excel Facts

Similar threads

Forum statistics

Share this page

Audio alarm/alert

mpfx

Board Regular

Legacy 98055

Guest

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock