Play Sound in Excel

[mgrech]

I found a nice piece of code that plays a sound when the cell value changes. It works well if the number in the inequality is an integer but it does not work if it is a decimal as per below. How do I fix it so that it works also for decimals?

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("F2:F250")
For Each Cell In CheckRange
If Cell.Value < -0.05 Then
PlaySound = True
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\ding.wav", 1)
End If

End Sub[/VBA]

[p45cal]

It works here, as you have it.
Realise, you have to put a value in less than negative 0.05, such as -1.
.004 will not trigger it.

[corfuen]

yes like the reply set you can deal it

[mgrech]

guys, i understand what you are saying but it is not working for me. problem is that it plays the sound every time there is a change, no matter what the new value is.

I have my criterion set to play sound if cell value < -0.05. If cell value is +3, it plays sound, if cell value is -1 it plays sound, if cell value is -0.04 it plays sound.

so as i said it is not working.

[Kenneth Hobs]

Are you "sure" that no value in your range meets the criterion?

Most people code it for just the cell(s) in a certain range. The way you do that is to use Intersect(). Dinging because I changed cell F2 when it is F3 that meets the criterion might lead one to think that F2 meet the criterion.

[vba]
Private Const SND_APPLICATION = &H80 ' look for application specific association
Private Const SND_ALIAS = &H10000 ' name is a WIN.INI [sounds] entry
Private Const SND_ALIAS_ID = &H110000 ' name is a WIN.INI [sounds] entry identifier
Private Const SND_ASYNC = &H1 ' play asynchronously
Private Const SND_FILENAME = &H20000 ' name is a file name
Private Const SND_LOOP = &H8 ' loop the sound until next sndPlaySound
Private Const SND_MEMORY = &H4 ' lpszSoundName points to a memory file
Private Const SND_NODEFAULT = &H2 ' silence not default, if sound not found
Private Const SND_NOSTOP = &H10 ' don't stop any currently playing sound
Private Const SND_NOWAIT = &H2000 ' don't wait if the driver is busy
Private Const SND_PURGE = &H40 ' purge non-static events for task
Private Const SND_RESOURCE = &H40004 ' name is a resource name or atom
Private Const SND_SYNC = &H0 ' play synchronously (default)

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Dim bPlaySound As Boolean

Set CheckRange = Range("F2:F250")
For Each Cell In CheckRange
If Cell.Value < -0.05 Then
bPlaySound = True
End If
Next Cell

If bPlaySound Then
'sndPlaySound32 "C:\windows\media\ding.wav", 1
PlaySound "C:\windows\media\ding.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
End If
End Sub[/vba]

[Kenneth Hobs]

Of course does the same.

[VBA]Private Const SND_ASYNC = &H1 ' play asynchronously
Private Const SND_FILENAME = &H20000 ' name is a file name

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

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Range("F2:F250"), "< -0.05") > 0 Then _
PlaySound "C:\windows\media\ding.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
End Sub[/VBA]

[p45cal]

<snip> it plays the sound every time there is a change, no matter what the new value is.

I have my criterion set to play sound if cell value < -0.05.

No you don't, it will play a sound if ANY cell in F2:F250 is less than -0.05, regardless of the cell you've just changed.

see if this behaves the way you want:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CellsJustChanged As Range, CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("F2:F250")
Set CellsJustChanged = Intersect(CheckRange, Target)
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then
PlaySound = True
Exit For
End If
Next
End If
If PlaySound Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
End Sub
[/VBA]or a bit shorter:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, CellsJustChanged As Range, CheckRange As Range
Dim PlaySound As Boolean

Set CellsJustChanged = Intersect(Range("F2:F250"), Target)
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
Exit For
Next
End If
End Sub
[/VBA]

[mgrech]

guys, thank you for your suggestions. i will try them.

that said, why does the original code work well when the inequality has an integer but it does not work when it has a decimal? I thought it has to do with defining the type properly ( I have fortran programming expereince but I am new to VBA)

[Kenneth Hobs]

Try making a simple example. Of course an integer versus a long or double type could cause a problem. What can trip you up is if your Excel data is double but you convert it to integer in vba and then compare that to a double. Understand too the differences between a vba Round and a worksheet function round. They do not work the same.

[p45cal]

[VBA]
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
Exit For
Next[/VBA]

I've just noticed an error in the above, it should be:[VBA]
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1):Exit For
Next[/VBA]

[mgrech]

attached is an example. you can see the numbers i have in column f.

[p45cal]

so try the attached

[mgrech]

thank you for sending me this. it is an improvement but i still have an issue.

in reality i have column f as a function of column d and column e where

column f = (column d / column e) - 1

i found out that if i change the value of column f manually then the program works. however if i update column d or column e so that column f updates , then the program does not work.

how can i modify it such that it works all the time?

[p45cal]

however if i update column d or column e so that column f updates

How actually are columns D/E being updated? by keyboard input? by link to other cells, perhaps on another sheet? by DDE link or some such.. How?

[mgrech]

i use a program called xlq which downloads data from yahoo finance so for example i will have a formula =xlq(MSFT,lastprice) and it would get the last trading price for MSFT.

that said, when doing the testing i was populating the values of columns d and e by hand, just by typing a number. i was not using the xlq formula. still the program did not respond well. it only works well if i make the change to column f directly.

[Kenneth Hobs]

[VBA]Set CheckRange = Range("F2:F250").Dependents [/VBA]

[p45cal]

[VBA]Set CheckRange = Range("F2:F250").Dependents [/VBA]

Kenneth, I don't think F2:F250 has any dependents. though it probably has .precedents.
Haven't got time to look at it right now, but I suppose something along the lines of:
[VBA]set checkrange = intersect(target.dependents,range("F2:F250"))
For each cll in checkrange.cells
if…[/VBA]
and then there'd be the question of whether the way data gets into the sheet will trigger the sheet_change event at all. It could be a case of using the calculate event and keeping a global variable version of the F2:F250 range (or a Static local variable of the same) to check against for changes.

[mgrech]

this last suggestion works. thanks p45cal

[mgrech]

one more question, if I may. How can I get the sound to keep playing until the user clicks ok? I tried using
Const SND_LOOP = &H8

but it does not seem to be making any difference?

[p45cal]

try:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, CellsJustChanged As Range
Set CellsJustChanged = Intersect(Target.Dependents, Range("F2:F250"))
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then
sndPlaySound32 "C:\windows\media\ding.wav", SND_ASYNC + SND_LOOP
Exit For
End If
Next
End If
End Sub
[/VBA]and the following event code for an activeX button I put on the sheet stops the sound.[VBA]Private Sub CommandButton1_Click()
sndPlaySound32 vbNullString, SND_SYNC
End Sub
[/VBA]