View Full Version : play a sound in excel
lior03
07-09-2006, 02:49 AM
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
07-09-2006, 03:11 AM
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
07-09-2006, 03:49 AM
I missed DRJ's KB item
http://vbaexpress.com/kb/getarticle.php?kb_id=161
Blunt
10-01-2009, 12:45 PM
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
10-01-2009, 01:38 PM
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
10-01-2009, 05:35 PM
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
10-07-2009, 10:47 AM
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
10-09-2009, 03:03 PM
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
10-10-2009, 08:48 PM
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.