Play Sound if value of Cell I9 INCREASES

[surkdidat]

Is there anyway I can make Excel play an Alert sound of some sort every time the value of Cell i( INCREASES? (Not if decreases) please?

 

[Joe4]

How is the value in Cell I9 updated?
Is it done manually (hard-coded)?
If it is a formula, please post the formula.

 

[surkdidat]

How is the value in Cell I9 updated?
Is it done manually (hard-coded)?
If it is a formula, please post the formula.

It contains a COUNTIFS Formula, which is linked to another sheet. (

So =COUNTIFS('[Tracker.xlsm]In Progress'!$J:$J,H9)

How is the value in Cell I9 updated?
Is it done manually (hard-coded)?
If it is a

 

[Joe4]

What I would store the most recent value in I9 in some unused cell.
Then every time a calculation is run on that sheet, compare the current value in cell I9 to the previous value (in our stored cell), and "beep" if it has increasted.

In order to do that, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Calculate()
'   Check to see if value in I9 increased
    If Range("I9") > Range("Z9") Then Beep
'   Store last value in Z9
    Range("Z9") = Range("I9")
End Sub

Note that I am using cell "Z9" to store the previous value of "I9". You can change that to any cell that you want.
Also note that this will "Beep" the first time you use it, as there is no value in the stored cell to start.

 

[surkdidat]

What I would store the most recent value in I9 in some unused cell.
Then every time a calculation is run on that sheet, compare the current value in cell I9 to the previous value (in our stored cell), and "beep" if it has increasted.

In order to do that, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Calculate()
'   Check to see if value in I9 increased
    If Range("I9") > Range("Z9") Then Beep
'   Store last value in Z9
    Range("Z9") = Range("I9")
End Sub

Note that I am using cell "Z9" to store the previous value of "I9". You can change that to any cell that you want.
Also note that this will "Beep" the first time you use it, as there is no value in the stored cell to start.

Thank you for this - is there any other sounds it can do the the standard "Beep" so I can differentiate it from when it just normally makes that sound?

 

[Joe4]

Sure you can play any sound you want, if you have a "wav" file for it.
See: Playing WAV-files using VBA in Microsoft Excel

 

[Eric W]

You can also use the Speech tool:

Application.Speech.Speak "it increased"

 

[surkdidat]

I am getting an error Message when it runs "28 - Out Of Stack Space" ?

Can you help?

 

[Joe4]

Try this variation:

Private Sub Worksheet_Calculate()
'   Check to see if value in I9 increased
    If Range("I9") > Range("Z9") Then Application.Speech.Speak "it increased"
'   Store last value in Z9
    Application.EnableEvents = False
    Range("Z9").Value = Range("I9").Value
    Application.EnableEvents = True
End Sub

 

[surkdidat]

Try this variation:

Private Sub Worksheet_Calculate()
'   Check to see if value in I9 increased
    If Range("I9") > Range("Z9") Then Application.Speech.Speak "it increased"
'   Store last value in Z9
    Application.EnableEvents = False
    Range("Z9").Value = Range("I9").Value
    Application.EnableEvents = True
End Sub

Superb - this coding seems to be holding up