Audio alarm/alert

[mpfx]

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]

Hi.
Very easy...

Copy this Declare into any standard module.<pre>
Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long</pre>

Call as such:<pre>
sndPlaySound32("C:WINDOWSMEDIAChimes.wav", 0)</pre>

This example will play the wave sound if cell A1 = 2.<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If [A1] = 2 Then Call sndPlaySound32("C:WINDOWSMEDIAChimes.wav", 0)
End Sub</pre>
Tom
This message was edited by TsTom on 2002-08-02 03:31

 

[mpfx]

Thanks Tom, will try it now

 

[mpfx]

Hi Tom,
As I am a total newbie to VBA I am a little uncertian about ' Call such as '
Do I paste this in a module ?
Also I need to monitor 8 cells, 4 for new high and 4 for new low.
Thanks again for you time or anyone else who maybe able to help.

 

[Legacy 98055]

Hi.
Please reply with the cell references.
Distinguish "High" and "Low".
Also, so that I might have a better understanding, NewHigh or NewLow values are contained in these same cells?
For example: LowValues in A1,A2,A3,A4
If any value is entered in any of the aforementioned which is lower than the same, play some Bach?
The reason I ask is this:
If the LowValue is not static then we will need to store these values elsewhere besides these same cells. If the value changes, and therefor eliminating the previous value, how will we know if it was lower or not? No big deal, but I will need to know exactly what you are doing before trying to dish out a working example customized for yourself.
Tom

 

[mpfx]

Hi Tom,
Thanks for the reply and your time.
I have live data for excel and want to monitor when a new high or low is reached.
The formula that I use for this is
In Cell B3 =IF(B2>A3,"New High","No")
A3 is previous high
B2 is live data cell

For low I use in cell D3 =IF(B2<C3,"New Low","No")
C3 is previous Low
B2 is live data

The cells I need to Monitor are as followed.
I have 4 seperate data cells which monitor currency rates live, so B2 = Eur/Usd, B4 = Usd/Yen, B6 = Usd/Chf and B8 = Gbp/Usd

Live data cell B2 with high in B3 and Low in D3
Live data cell B4 with high in B5 and low in D5
Live data cell B6 with high in B7 and low in D7
Live data cell B8 with high in B9 and low in D9

I also wish to use the exlode.wav file which I have copied to the windows media folder from the office media folder. ( Need something that wakes me up..Lol )

I hope this info is enough, many thanks again for your time and help..

Pete

 

[Legacy 98055]

Hi.
Right-click on your worksheet, choose view code, and paste this in:<pre>
Private Sub Worksheet_Calculate()
Dim PlaySound As Boolean
Application.EnableEvents = False
If [B3] = "New High" Then
PlaySound = True
[A3] = [B2] 'assign new high value
ElseIf [B5] = "New High" Then
PlaySound = True
[A5] = [B4]
ElseIf [B7] = "New High" Then
PlaySound = True
[A7] = [B6]
ElseIf [B9] = "New High" Then
PlaySound = True
[A9] = [B8]
ElseIf [D3] = "New Low" Then
PlaySound = True
[C3] = [B2]
ElseIf [D5] = "New Low" Then
PlaySound = True
[C5] = [B4]
ElseIf [D7] = "New Low" Then
PlaySound = True
[C7] = [B6]
ElseIf [D9] = "New Low" Then
PlaySound = True
[C9] = [B8]
End If
If PlaySound Then Call sndPlaySound32("C:WINDOWSMEDIAexplode.wav", 0)
Application.EnableEvents = True
End Sub


Make sure you remove the double / to single forward slashes. From this same window, from the menu bar up top, choose Insert > Module.
Paste this declare in:<pre>
Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long</pre>

I was unsure as to how you updated the new highs and lows, so I went ahead and included an automatic update in the procedure. I am hoping that I understand your structure correctly???

Try it out and we'll fix it if necc.

Tom</pre>
This message was edited by TsTom on 2002-08-02 23:31

 

[mpfx]

Hi Tom,
Have done as u said but no sound, no errors or debug either?
I changed the back slashes to forward of the windows/media/explode, if that is what u meant?
If it is any easier I am happy to create a new sheet with live data in cells B1,B2,B3,B4
I need the highest and lowest levels recorded for each of those cells and explode sound played when a new high or low is achieved.
Again thanks for your help, I wish I could return the favor somehow.
Pete

 

[Legacy 98055]

Can I just email you the workbook?
Tom

 

[mpfx]

yep sure TOM, mpfx@stideas.com

Thanks again