play a sound in excel

[lior03]

hello
is it possible to make excel sound every time a cell in a selection exceed a certain value?.
i hav a sound file i want to activate every time it happens.
thanks

[mdmackillop]

Hi Moshe,

from http://www.mrexcel.com/archive/VBA/13716b.html

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target > 10 Then PlayWavFile "C:\Splash.wav", True
End Sub




put this code in a module:

Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Public Sub PlayWavFile(WavFileName As String, Wait As Boolean)
If Dir(WavFileName) = "" Then Exit Sub 'no file to play
If Wait = True Then 'play sound before running any more code
sndPlaySound WavFileName, 0
Else 'play sound while code is running
sndPlaySound WavFileName, 1
End If End Sub

[mdmackillop]

I missed DRJ's KB item
http://vbaexpress.com/kb/getarticle.php?kb_id=161

[Blunt]

Hello, I am running Excel 2003, and have very little programming experience, :whip

I am a trader and have set up a simple spreadsheet that is fed real-time market data.

My eyes get sore because I currently have the sheet coded to flash "Buy" or "sell" every time the condition is met and I have to constantly monitor this and numerous other things...I'd like it to play a sound....

Here's the details:

I have the "real-time" data flowing into and updating cell X every second, and then a constant value in cell Y that I input before the market opens. I want an alarm of some kind to go off EVERY time X is less than Y....even if it happens 5 times per minute.

I tried the code in this thread, and a few other threads I found running a search, and its not working at all.

Any help would be appreciated :help :beerchug:

If you want to see my sheet and an explanation of how it works, I would be glad to send you my proprietary work...yes my eyes are that tired and in need of audio alert.

[mdmackillop]

Try this, the code monitors D4 and compares it to G4
Change this code in the Sheet1 code module to point to your sound file

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4") > Range("G4") Then PlayWavFile "C:\AAA\flyby2.wav", True
End Sub

[Blunt]

Try this, the code monitors D4 and compares it to G4
Change this code in the Sheet1 code module to point to your sound file

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4") > Range("G4") Then PlayWavFile "C:\AAA\flyby2.wav", True
End Sub



Thank you for reply, I'll let you know first thing tomorrow morning with live data how it goes.

[Blunt]

Thank you for reply, I'll let you know first thing tomorrow morning with live data how it goes.
mdmackillop: your program worked beautifully; my spreadsheet, on the other hand, is still not functioning.

It plays the sound once (if the condition is true) when the reference value is entered...then not ever again.

I am attaching file

1974

I tried adding a loop, but that didn't do it either.

Thanks again for your help :thumb

[Paul_Hossler]

Any time I change a cell on Sheet1 while the condition is true, it plays a sound.

Did you want it to play continously while the condition is true? (Sounds real annoying to me :rotlaugh: ), or maybe every 15 seconds while the condition is true?

Is it only those 2 cells you're comparing?

Like Mac said, you can use OnTime. Example:

In the WB module


Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CheckStop
End Sub
Private Sub Workbook_Open()
Call CheckStart
End Sub


In a regular module:


Option Explicit
Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Public dTime As Date
Public bChecking As Boolean
Public Sub PlayWavFile(WavFileName As String, Wait As Boolean)
If Dir(WavFileName) = "" Then Exit Sub 'no file to play
If Wait = True Then 'play sound before running any more code
sndPlaySound WavFileName, 0
Else 'play sound while code is running
sndPlaySound WavFileName, 1
End If
End Sub

Sub CheckStart()
Application.StatusBar = "Checking started"
bChecking = True

Call CheckCells
End Sub

Sub CheckCells()
If Worksheets("sheet1").Range("D9").Value < Worksheets("sheet1").Range("B4").Value Then
Application.StatusBar = "Exceeded"
PlayWavFile "C:\windows\media\tada.wav", True
End If

If bChecking Then
Application.StatusBar = "Next Check at " & Format(Now + TimeValue("00:00:05"), "hh:mm:ss")
Call Application.OnTime(Now + TimeValue("00:00:05"), "CheckCells")
DoEvents
End If

End Sub

Sub CheckStop()
bChecking = False

Application.StatusBar = "Checking stopped"

End Sub


Basically what it does is wake up every 5 sec, check the condidtion, and 'set it's own alarm to wake up and check in 5 sec, etc.

The CheckStart and CheckStop are just to start and stop it



Paul

[Blunt]

Any time I change a cell on Sheet1 while the condition is true, it plays a sound.

Did you want it to play continously while the condition is true? (Sounds real annoying to me :rotlaugh: ), or maybe every 15 seconds while the condition is true?

Is it only those 2 cells you're comparing?

Like Mac said, you can use OnTime.
Paul

Hey, thanks Paul that is some substantial coding there

I do want it to play continuously, but in reality the value is changing rapidly in such a manner that I doubt it would beep more than 3 times consecutively.

Cell D9 is compared with the other B-cell, for the buy signal....but I think I can add that code in.

Thanks again fellas :beerchug: