Solved: Create a sound

[maninjapan]

Im trying to create a sound warning when a cell exceeds a certain value. Learning the steps to this are as important as the final outcome for me so I plan to add to it piece by piece. First step is simply getting a beep when A1 > A2 which I have done using the following as a base. I would now like to add change the beep to a .wav file and havea message box pop up at the same time.

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value < 25 Then
For x = 1 To 5
Beep
Application.Wait Now + TimeValue("00:00:01")
Next x
End If
End If
End Sub

[/vba]

Thanks!!

[GTO]

Hi maninjapan,

Take a look at http://www.vbaexpress.com/kb/getarticle.php?kb_id=161

[maninjapan]

Ok, Ive got a bit further with this. with a bit of cut and paste from another sheet I found so its probably pretty ugly but it seems to work so far. I have a sound alert with a popup window. I want the wav file to play until the popup window is closed.

Sheet 1 B6 is the cell Im working with for now.

Any help with this would be much appreciated.

Thanks!!

[maninjapan]

GTO, thanks, looks like I posted this at the same time. I'll have a look at this too! Thanks!!

[maninjapan]

GTO, thanks, tried this, but it with MsgBox but it only plays the file after I press ok in the message box. I'd like it to play the sound and show the message box at the same time and keep playing until OK is clicked

[GTO]

I do not have handy how to cancel the sound. To get the box up while the sound plays, change the second arg to 1
[vba]Sub PlaySound()
'//
Call sndPlaySound32(ThisWorkbook.Path & "\LoadIt.WAV", 1)
MsgBox "HI"
End Sub[/vba]

[maninjapan]

Currently using this, looks like it is set to 1, but still playing after I click ok
[VBA]

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)

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

Set CheckRange = Range("B15")
For Each Cell In CheckRange
If Cell.Value > 10 Then
PlaySound = True
MsgBox "TEST MESSAGE"

End If
Next
If PlaySound Then
Call sndPlaySound32("C:\tt\sounds\imo.wav", 1)
End If

End Sub
[/VBA]

[GTO]

In #7 we appear to be checking B15's value each time any change is made to the sheet. Is that the goal?

[GTO]

I hope I'm not terribly off here, but maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("B15")) Is Nothing Then
        If Range("B15").Value > 10 Then
            Call sndPlaySound32(ThisWorkbook.Path & "\LoadIt.wav", 1&)
            If MsgBox("Kill that dang sound!", vbOKOnly Or vbQuestion) = vbOK Then
                Call sndPlaySound32(0&, 1&)
            End If
        End If
    End If
End Sub

This should just fire when B15 changes (I are getting a wee bit tired), and cancel the sound if the msgbox clears before the sound ends.

[maninjapan]

good one GTO. Nailed it!!

[GTO]

Have a great weekend!

[Kenneth Hobs]

To add to GTO's fine example, I added an embedded wav file method. I also tweaked GTO's code for another example to show how to loop the sound file playing and to download the file if needed.

[maninjapan]

Thank you Kenneth, Ive been able to add the loop sound file to my code. My next question is, once the alert has been triggered and stopped I dont want it to trigger again until it has gone back below and then breached the trigger range again. (I hope this makes sense) I am using this to create a trigger for my trading P&L and only need it to go ff once per breach of the trigger level.

Thanks

[Kenneth Hobs]

Does the Change event not meet your need?

[maninjapan]

Kenneth, sorry if I wasnt clear. I use this as an alert for my trading PL, I just need it to alert me the first time it goes past a certain amount. Once it trips I want to just be able to turn it off and not have it trigger strainght away (the first alert already got my attention). Hope that makes sense.

If this makes the macro a lot more difficult, perhaps I could just add a 2nd condition (If B14 = "ON" or a check box or similar) that way I could just turn it on/off as I need it.

[Kenneth Hobs]

In my routines:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B15")) Is Nothing Then Exit Sub
If Range("B15").Value <= 10 And Range("B14").Value2 <> "ON" Then Exit Sub
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
tNow = 0
Unload Me
Range("B14").Value2 = "OFF"
End Sub[/VBA]

[maninjapan]

Thanks again Kenneth, I tried to work that idea into the following, the idea being the alarm wont execute as long as "ON" is not entered into G2. It seems to be ignoring that however, and executing every time.
[VBA]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tf As Boolean, soundFile As String
If Application.Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Range("B4").Value > Range("G3").Value And Range("G2").Value2 <> "ON" Then Exit Sub
soundFile = "C:\tt\sounds\alarm2.wav"
Call sndPlaySound32(soundFile, SND_LOOP Or SND_ASYNC)
If MsgBox("Kill that dang sound!", vbOKOnly Or vbQuestion) = vbOK Then
Call sndPlaySound32(0&, 1&)
End If
End Sub
[/VBA]

[GTO]

Using Ken's wb (Nice! I don't recall seeing playing an embedded clip), and other than changing the path to the sound file, you code worked for me. If you want to 'flip a switch' once the code runs once, maybe try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tf As Boolean, soundFile As String
    
    If Not Application.Intersect(Target, Range("B4")) Is Nothing _
    And Range("B4").Value > Range("G3").Value _
    And Not Range("G2").Value = "ON" Then
        Application.EnableEvents = False
        Range("G2").Value = "ON"
        Application.EnableEvents = True
        soundFile = ThisWorkbook.Path & "\YesMaster.wav"
        Call sndPlaySound32(soundFile, SND_LOOP Or SND_ASYNC)
        
        If MsgBox("Kill that dang sound!", vbOKOnly Or vbQuestion) = vbOK Then
            Call sndPlaySound32(0&, 1&)
        End If
    End If
End Sub

[maninjapan]

Thanks GTO that does what I need. Now I am just having one small issue, "B4" is an RTD link that connects to my trading software. When I initially open the sheet it takes a couple of seconds to get data. Until then it shows #N/A at which time I get an error message related to the value. Is there a simple IF statement that will return '0' unless the cell returns an actual number?

Thanks

[GTO]

I am afraid I do not have any experience (or know what it is) as to an RTD link. I presume the change event is being fired(?) rather than the calculate event. Anyways, a blind stab - maybe try testing for IsError and assign value to a variable dependant on outcome.

Dim MyVal As Double
    
    If IsError(Range("B3")) Then
        MyVal = 0
    Else
        MyVal = Range("B3")
    End If