PDA

View Full Version : Solved: Sound alert



maninjapan
06-17-2010, 10:49 AM
I'm trying to setup a sound alert for when a cell's value exceeds a certain value. Ive tried the following and it seems to work ok when I run it as a macro, but this doesnt help me much.
I would like it to just run constantly, as part of the spreadsheet, to alert me when a dynamically changing cell value (its a stock price) exceeds a certain value and will continue to run until the value goes back under the alert prcie or is stopped (planning to add a popup with a stop button later)


'========================================================================== ======
'- SIMPLE METHOD : PLAY A WAV FILE
'- Requires winmm.dll
'- Brian Baulsom October 2008
'========================================================================== ======
Private Declare Function mciExecute Lib "winmm.dll" _
(ByVal lpstrCommand As String) As Long
Dim WAVfile As String
'========================================================================== =======
'- CHECK CELL VALUE
'========================================================================== =======
Sub CheckCellValue()
If Range("A1").Value > 99 Then
PlayFile
End If
End Sub
'========================================================================== =======
'- PLAY THE FILE
'========================================================================== =======
Sub PlayFile()
WAVfile = "C:\Sounds\Sound.wav"
mciExecute ("play " & WAVfile)
End Sub

mdmackillop
06-17-2010, 12:40 PM
This uses a timer (http://www.cpearson.com/excel/OnTime.aspx) to change a value and trigger your sound code.

maninjapan
06-17-2010, 12:57 PM
thats great md, is there a simple way to continuously play the wav file until the condition changes?

maninjapan
06-17-2010, 01:06 PM
Just tried to copy the alert code to a new spreadsheet, but it doesnt react to a change in cell A1, do I need to copy something else across from your sheet? I dont need the number generator section

Actually , just figured it out. Was able to replicate it in a new sheet.

mdmackillop
06-17-2010, 01:16 PM
Set the timer to suit the wav length and call the start command from the called procedure.


Sub TheSub()
''''''''''''''''''''''''
PlayFile
''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub

maninjapan
06-17-2010, 01:33 PM
sorry md, I might not be understadning this, I changed the Interval seconds to 20, but it still only plays once

mdmackillop
06-17-2010, 01:37 PM
Can you post your own code, if it's not confidential.

maninjapan
06-17-2010, 01:53 PM
I just actually adjusted the sheet you posted, changing hte time to 50 seconds, however the wav file only places once.
I also tried manually changinfthe value of the cell, it only plays the file once as well

maninjapan
06-18-2010, 03:26 AM
Tried this again today, and it worked fine. Thanks a lot for your help

Shred Dude
06-22-2010, 11:25 PM
Another fun alternative can be to use the Speech Object.

application.Speech.Speak "Hey, look at this."

Cyberdude
06-23-2010, 04:24 PM
If you are using Windows 7 and Excel 2007 I don't think that Speech is available. I think I read somewhere that Microsoft removed it in the new systems. You might want to check it out.
Sid
PS. Also you might try the Beep command.

Shred Dude
06-23-2010, 09:16 PM
I use it on Win 7 with Excel 2007. Still available.

As of Vista, the voice changed to a more female version as compared to the XP voice.