Excel Sound Alert Request

Hello Experts,

I wonder if someone could provide a formula to sound an alert when cell AQ3 and/or cell AV3 reaches a value of 85.

Please note, this needs to alert when the value changes dynamically not through a manual alert. I also forget to make that distinction when asking for a sound alert.

Cheers


Carlton
EE-Nasdaq-CRSv7.xlsm
cpatte7372Asked:
Who is Participating?
 
dlmilleCommented:
My apologies - at the last minute, I renamed the module to soundAlert - the same name as the subroutine!  fixed.  Sorry about that!

See attached,

Dave
EE-Nasdaq-CRSv7r2.xlsm
0
 
cpatte7372Author Commented:
Oh, and please can you ensure that the alert stops after the value changes from 85.

Cheers
0
 
dlmilleCommented:
Ok - when the calculate happens, I added code to chek the values of both AQ3 and AV3, if 85 or greater, it will chime the alert.  Everytime a calculate happens, this will happen.  When the value is no longer >= 85, the chimes won't happen.

Here's the code:
Private Sub Worksheet_Calculate()
Dim cel As Range
Dim Addr As Variant, Targ As Variant
Static Stocks(3) As Double      'Starts with element 0
Dim i As Long, n As Long


    'Added code
    If ActiveSheet.Range("AQ3").Value > 85 Or ActiveSheet.Range("AV3").Value > 85 Then
        Call soundAlert(True)
    Else
        Call soundAlert(False)
    End If

    Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
    Targ = Array(85, 85, 20, 20)                'Look for prices above these threshhold values
    n = UBound(Stocks)
    For i = 0 To n
        With Range(Addr(i))
            If Not IsError(.Value) Then
                If Stocks(i) <> .Value Then
                    Stocks(i) = .Value
                    If .Value > Targ(i) Then
                        Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                        Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                        Close 1
                    End If
                End If
            End If
        End With
    Next i

End Sub

Open in new window


For the chime, I used the tip from Chip Pearson's website - its in the soundAlert Module I created.  Here's the site:http://www.cpearson.com/excel/PlaySound.aspx

You can play any WAV file you have on your Desktop for the alert.  I used chime from Chip's example to get this going.

See attached.

Enjoy!

Dave
EE-Nasdaq-CRSv7r1.xlsm
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cpatte7372Author Commented:
Hi Dave,

Going to play around with it now.....
0
 
cpatte7372Author Commented:
Dave,

I changed a variable and I got the following error message

Compile Error:

Expected variable or procedure, not module.

And then it points to

Call soundAlert(True)
0
 
cpatte7372Author Commented:
No worries mate.

Going to check it out now....
0
 
cpatte7372Author Commented:
Dave,

I exported the 'soundAlertModule' and then imported it into my live spreadsheet. However, it won't work. Is there something that I'm missing? I can't see any difference between the attached spreadsheet and my live spreadsheet.

Cheers
0
 
dlmilleCommented:
First - did what I post work for you, as-is?

Dave
0
 
dlmilleCommented:
And did you copy over the Worksheet_Calculate() subroutine, overwriting the existing one?

Dave
0
 
cpatte7372Author Commented:
Uhmmmm, I don't believe I did - not sure what it is. I'll check
0
 
cpatte7372Author Commented:
Found it. Copied it over and now its working fine.

I'm sure it's fine but will put it to the test when the stock markets open.

In the meantime, as always Dave, thank you very much mate.
0
 
cpatte7372Author Commented:
Wicked mate....

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.