audible alarm macros

[dkoco]

I have an excel spreadsheet that needs to have an audible alarm soun
when a condition is met. Right now I just use the conditiona
formatting button to have it change to a color. How do I add
macro(and do you have a sample macro) so it makes an audible alarm
Thanks in advance.

Dave

(e-mail address removed)

 

[Sharad Naik]

You use just Beep command to generated a Computer Beep.
Or you can use Shell command to play a sound file.
Below example plays reminder.wav file using Windows Media Player.
, it is assumed that reminder.wav file is in directly in C: drive.

Dim palySound
playSound = Shell("C:\Program Files\Windows Media Player\wmplayer.exe
c:\reminder.wav", vbHide)

Sharad

 

[dkoco]

I'm a newbie so help me out. My spreadsheet is very simple(no vba). Ho
do I add the alarm macro? I have a cell that uses conditiona
formatting to turn it a color. How do I make a sound/alarm also? I ow
a trading company and may need a programmer/developer. Please email m
at (e-mail address removed) with answers. There is a possible jo
opportunity also.

Dav

 

[mangesh_yadav]

can't remember from where I picked this up, but here it is. In a ne
module paste the code below

'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\phone.wav" 'Edit thi
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function


Then you can call the function in a cell as follows:
=Alarm(A1,0)

where A1 is the cell for which you want to keep an alarm and 0 (coul
be anything else) is the condition you want to keep.

- Manges

 

[Rob van Gelder]

Here's a clunky way.
I say clunky cause I don't really like bells and whistles in Excel. This
rates around the same as blinking cells for me.


A user defined Function.
Put this in a code module.

'---
Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long

Public Function PlayWavFileIF(WavFile As String, Condition As Boolean) As
Boolean
Const SND_ASYNC = &H1, SND_FILENAME = &H20000
If Condition Then PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFileIF = Condition
End Function

'---

Then you can use it like a cell formula.
So in A1:
=PlayWavFileIF("C:\WINDOWS\Media\tada.wav", B1=1)
Then the audio only plays if B1 is equal to 1

In a conditional format, it works similar:
Condition 1: FormulaIs: =PlayWavFileIF("C:\WINDOWS\Media\tada.wav", B1=1)

 

[dkoco]

Rob,

Thanks for your help. I am attaching a very small exce
spreadsheet(only has 3 cells). What I am trying to do is sound an alar
when column 4 is equal to 11. If you could attach your clip in a v
macro(something I dont know how to do yet..learing quick though). Th
numbers 5 and 6 will be changing all the time on my big spreadsheet(it
a trading system). Thanks again for all your help.

Dave

here is small spreadsheet:its simply adding cell 1 and 2.

cell 1 cell 2 cell 3
5 6 1

 

[dkoco]

davetrades@yahoo. com is my email

 

[Rob van Gelder]

Assuming Cell 1 is A1 and Cell 2 is B1, Cell 3 will contain a formula:
=A1+B1
D1 will contain this formula: =PlayWavFileIF("C:\WINDOWS\Media\tada.wav",
C1=11)

To insert the code in the workbook:

From the Excel menu: Tools | Macro | Visual Basic Editor
(or use the shortcut Alt+F11)

From the VB menu: Insert | Module
Copy that code in.