.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!

 

[VoG]

When I tested it, it played intel.wav when the value was over 75 and tardis.wav when the value was over 100.

 

[saint_2008]

It works for me. This should do what you want (if you can get the sounds to play!). Note that I changed the filenames and path of the .wav files.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim FName As String
If Target.Address(False, False) = "S41" Then
    Select Case Target.Value
        Case Is > 99: FName = "C:\tardis.wav"
        Case Is > 75: FName = "C:\intel.wav"
        Case Else: FName = ""
    End Select
    If FName <> "" Then Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End If
End Sub

Yer its finally working like it should Thanks for all your help! its been a great help

 

[saint_2008]

VoG II is a VB GOD!

 

[bloomer89]

Try this in the worksheet code module instead of what you had previously

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("S41").Value
    Case Is > 99: FName = "C:\tardis.wav"
    Case Is > 75: FName = "C:\intel.wav"
    Case Else: FName = ""
End Select
If FName <> "" Then Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

In regards to this code, how do you arrange the Select Case Range to look at a column of values (ex. I4:I50) rather than the single cell S41? And are any further modifications required to allow for this?
Thank you.

 

[VoG]

Try this and note the change in red.

Private Sub Worksheet_Calculate()
Dim FName As String, c As Range
For Each c In Range("I4:I50")
    Select Case c.Value
        Case Is > 99: FName = "C:\tardis.wav"
        Case Is > 75: FName = "C:\intel.wav"
        Case Else: FName = ""
    End Select
    If FName <> "" Then Call PlaySound(FName, 0&, SND_SYNC Or SND_FILENAME)
Next c
End Sub

 

[bloomer89]

VoG,
Thank you very much for the quick reply and solution, those changes did the trick. Having this feature in my spreadsheet is going to be a life-saver!

 

[BrianB]

Here is a simple way.

'================================================================================
'- 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("S41").Value > 99 Then
        PlayFile
    End If
End Sub
'=================================================================================
'- PLAY THE FILE
'=================================================================================
Sub PlayFile()
    WAVfile = "C:\windows\media\ding.wav"
    mciExecute ("play " & WAVfile)
End Sub
'==================================================================================

 

[Legacy 76526]

Hi VoG and others,

I am trying to do something similar and have ripped my hair out trying to get things to work... but no luck.

I have dynamic data coming into to my spreadsheet from the Australian stock exchange and the worksheet updates automatically with data. I have the spreadsheet operating so that new opportunities appear automatically in rows (should the opportunity no longer meet criteria, it is automatically deleted from this worksheet), such that I have totalled the number of opportunities at anyone time to a sum in cell "Y1" (which has the formula [=countif(X2:X300, ">0")]). What I am after is a sound to play whenever cell Y1 increases in value only, so that should I be looking at a different screen, I will then know that a new opportunity has arisen.

I have tried the codes provided by VoG but modified to my circumstance:



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 > 0: FName = "C:\Windows\Media\Notify.wav"
Case Else: FName = ""
End Select
If FName <> "" Then Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End Sub


This uses the Worksheet_Calculate() event function which is meant to work only when the formula calculates a change in value (isn't it?) ... but no, I get sounds played whenever ANY cell is changed manually!!

Similarly the Worksheet_Change event too. Sounds play whenever ANY cell changes.

So why is it that I have specified Y1 as my target cell, but a change in any cell causes the sound to play?

I have also tried Brian's suggestion too, but it doesnt play sounds when cell Y1 changes value. (Although it does play sounds when I run it in the VB code area).

Once I get it to work for a change in value in cell Y1, I will then tackle the next part - to only play when cell Y1 increases in value.

Cheers for anyone's assistance.

Brad.

 

[VoG]

Try this

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

You may need to change Y2 to an unused cell.

 

[Legacy 76526]

Hi VoG,

Great work so far ... you have managed to make it play sound when only cell Y1 changes value. And it works with dynamic data too!!

I changed Y2 to the unused cell Z1. I can see what you are trying to do with the Z1 cell, but I have to say that its not quite working. Z1 only increases, thus when Y1 decreases (as opportunities no longer meet the criteria), it wont play a sound until Y1 is larger than Z1.

Somehow I have to get Z1 to drop in value as Y1 drops without making a sound. I have also noticed that when I select Z1 and press delete, it reverts to the cell value of Y1 (and then makes a sound).

Its like 4.30 am in Oz, need some sleep. Will give it some more thought tomorrow.

Thanks again for the code you provided above.

Brad.