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

 

[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

I'm I putting this as a mod or an active worksheet?

 

[VoG]

Well, you need the whole lot

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)
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

and it goes in the worksheet code module.

 

[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

This is still not working for me! though it might be the sheet so created a new one! I don't know what I'm doing wrong with it

 

[VoG]

Two thoughts:

1) What is changing the value of S41? If you enter a value in S41 then the code should run. If S41 contains a formula then you would need to use the Worksheet_Calculate() event.

2) Are sounds enabled? Try the following in a standard module:

Sub a()
MsgBox Application.CanPlaySounds
End Sub

If this returns True then sounds are enabled.

 

[saint_2008]

Two thoughts:

1) What is changing the value of S41? If you enter a value in S41 then the code should run. If S41 contains a formula then you would need to use the Worksheet_Calculate() event.

2) Are sounds enabled? Try the following in a standard module:

Sub a()
MsgBox Application.CanPlaySounds
End Sub

If this returns True then sounds are enabled.

Yer its true. and my sound is turned on and working checked that too!

What it does is, S41 gets its info from C40:G40 this is one merged cell, does it with a simple "=C40"

Now C40:G40 gets its info from =SUM(C38,G38) and This is from lots of Tick boxes that give out numbers depending wish ones you tick in what order!

 

[VoG]

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

 

[saint_2008]

ok still not! so Iv stripped all other codes from that area so no other macros or vb scripts are running, also I've made sure its in the right area and to clarify its

-VBAProject (myworkbook.xls)
-Microsoft Excel Objects
- Sheet
- this workbook
-Modules
- Module1 <<< (code is here!)




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

Iv took the C:\ off the files as there in the same directory would this work? only reason is I don't have access to the C:\ drive totally to add files.

hence me using the windows one before! but im trying to keep your code as it is with out altering it too much

 

[saint_2008]

what if I refer them to play a file with in excel ? for example "WindowsMediaPlayer1" that I place in as an object? only thing is I don't know the code for that!

 

[VoG]

The code is in the wrong place.

Right click the sheet's tab, select View Code and put it in there.

 

[saint_2008]

The code is in the wrong place.

Right click the sheet's tab, select View Code and put it in there.

ah I had it there originally I misunderstood when ya said put it in the Module

ok it works now kinda! lol it plays it when it goes to past 75 but not when it gets to 100

is away it only plays once when it goes over 75 and some how to make the 2nd wave play atm the intel one still plays not the tardis for the >99 ?