ACTIVATING A SOUND ALERT ON CELL VALUE

[SOLI]

MY EXCEL BOOK RECEIVES ON-LINE QUOTES FROM A DDE LINK.
HOW DO I SET UP AN ALERT TO ACTIVATE A SOUND (OR EXECUTE A PROGRAM ) WHEN
THE CONTENT IN A CERTAIN CELL REACHES A PRE-DETERMINED VALUE?
I AM NOT TOO FAMILIAR WITH VBA BUT USUALLY I MANAGE TO COPE WITH SIMPLE
MACROS.TKS.

 

[Gary''s Student]

If DDE refreshes A1, then in another cell (say B1) enter:
=A1
This insures that a calculation monitors the value. Finally install the
following macro in the worksheet code area:

Private Sub Worksheet_Calculate()
If Range("B1").Value > 100 Then
Application.Speech.Speak "The price is right"
End If
End Sub

So if the DDE puts a proper value in A1, B1 will "see" it and the alert will
be issued.

 

[Wendy]

That is HI-Larious!!! I just tried it and it worked.. Would love more of
those kinds of macros..

 

[SOLI]

Gary's Student
Beautiful ! It works.Thanks a lot.So simple and short.
Maybe you can expand a little more , telling me how to make the speech
repeat itself either a few times and/or continuously until I stop it by
hitting any key.

Also how can I change the value that activates the macro(the 100) to any
other number without having to edit the macro.If I could place this new
number(the substitute for the number 100) in cell C1 for example, it would be
easier.
How can I put a second macro for a different cell to be monitored?In the
same code?
Soli

 

[Gary''s Student]

This version allows you to put the number in cell C1
It also repeats the message 4 times.

Private Sub Worksheet_Calculate()
If Range("B1").Value > Range("C1").Value Then
For i = 1 To 4
Application.Speech.Speak "The price is right"
Next
End If
End Sub

 

[SOLI]

GREAT! it works well.
may abuse for more?
Is it possible to put more than 1 alert in a sheet? Say you want to monitor
cell A1 and F1...

 

[Gary''s Student]

Very similar.

I will update this post with a more general solution tomorrow.

 

[SOLI]

since you are going to update to a general solution,maybe you could include a
choice of what the alert will speak .It could speak the number contained in
the cell B1 instead of a generic unspecified "The price is right".Please
maintain the generic alert also.
soli

 

[Gary''s Student]

O.K.

 

[SOLI]

Sorry to barge in again.
Yesterday the DDE link was not available so I tested the macro in a simple
Excel sheet. however , to-day I tested it with the real thing and since the
imput is continuous(stream) the ^price is right^ every second and it did not
stop speaking that. Probably there has to be a brake so we can control how
many times the speech (or in new version - the quote )is repeated .
Also it is not enough that C1should indicate the value to be monitored, the
sign <=> also should be able to be substituted within C1 or C2.
soli

 

[SOLI]

Hi "Gary''s Student"
Are you still interested in helping out with the rest of the macros? I' d
sure apreciate it much.
Solil