• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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
0
cpatte7372
Asked:
cpatte7372
  • 8
  • 4
1 Solution
 
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
 
cpatte7372Author Commented:
Hi Dave,

Going to play around with it now.....
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now