Solved

Excel Sound Alert Request

Posted on 2011-09-05
12
276 Views
Last Modified: 2012-05-12
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
Comment
Question by:cpatte7372
  • 8
  • 4
12 Comments
 

Author Comment

by:cpatte7372
Comment Utility
Oh, and please can you ensure that the alert stops after the value changes from 85.

Cheers
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:cpatte7372
Comment Utility
Hi Dave,

Going to play around with it now.....
0
 

Author Comment

by:cpatte7372
Comment Utility
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
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
 

Author Comment

by:cpatte7372
Comment Utility
No worries mate.

Going to check it out now....
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:cpatte7372
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
First - did what I post work for you, as-is?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
And did you copy over the Worksheet_Calculate() subroutine, overwriting the existing one?

Dave
0
 

Author Comment

by:cpatte7372
Comment Utility
Uhmmmm, I don't believe I did - not sure what it is. I'll check
0
 

Author Comment

by:cpatte7372
Comment Utility
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
 

Author Closing Comment

by:cpatte7372
Comment Utility
Wicked mate....

Cheers
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now