.wav help for vb

[saint_2008]

This is what I have,

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

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If UCase(ActiveSheet.Range("S41").Value) > 99 Then
FName = "C:\windows\media\ding.wav"
Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End If
End Sub

but I cant get it to work!

All I want is for it to play a sound when My Value in S41 goes over the Value 99
the value is part of a simple formula of sums.

Cheers for any help, and pointing to the right direction where I'm going wrong with this!

 

[Legacy 76526]

Hi VoG,

Thanks your assistamce in getting the code to work. After some sleep I managed to figure the rest out. Now my macro makes a sound whenever the value in a cell increases, but not when it decreases when dynamic data is altering the value of this cell. I shall not miss a stock market trading opportunity again. Here is the remainder - as you can see, I added a little bit to make Z1 equal Y1 when Y1 decreases.

Code:
Option Explicit

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

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000


Private Sub Worksheet_Calculate()
Dim FName As String
Select Case Range("Y1").Value
Case Is > Range("Z1").Value: FName = "C:\Windows\Media\Notify.wav": Range("Z1").Value = Range("Y1").Value
Case Else: FName = ""
End Select
If FName <> "" Then Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
Select Case Range("Z1").Value
Case Is > Range("Y1").Value: Range("Z1").Value = Range("Y1")
Case Else: FName = ""
End Select
End Sub


It works ... and I now have my hair back :D If only you were here in Perth, you could have had the pint of Guiness I bought for you in appreciation for your help. It was delicious.