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

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

[/vba]


put this code in a module:
[vba]
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
[/vba]

[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,

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

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
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4") > Range("G4") Then PlayWavFile "C:\AAA\flyby2.wav", True
End Sub

[/vba]

[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
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D4") > Range("G4") Then PlayWavFile "C:\AAA\flyby2.wav", True
End Sub

[/vba]

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

Attachment 1974

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

Thanks again for your help

[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 ), 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

[VBA]
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CheckStop
End Sub
Private Sub Workbook_Open()
Call CheckStart
End Sub
[/VBA]

In a regular module:

[VBA]
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
[/VBA]

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 ), 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