double sound alert for on-line stream

[PAT]

can anyone help design a macro that serves as a sound & message alert in
Excel sheet?

It would have to be able to monitor the value in cell A1 (that comes in as a
feed from a DDE link - continous streaming). The alert would be activated
when the value on A1 matches the criteria in cell C1 and according to the
sign ( > ,< or = ) on D1, e.g. A1 > C1 or A1 < C1 or A1 = C1 or =< C1 etc...
When activated, the macro would speak up the value on C1 - say " the quote
is (value of C1) " - and would repeat 4 times the announcement and show a
message saying the same thing, until an OK button is hit.
Then all over again for one more alert but for a different cell to be
monitored, A2, to be matched with C2 according to the sign on D2 , all on the
same sheet as A1 etc..
The catch is that since the value is streaming continuously - say every
second, the alert will be activated continously every second ( independently
of the repetition).There would have to be a brake to make the alert stop when
A1 hits the value of C1 until it is reset by punching an OK button!
Much appreciate any help on that...
PAT

 

[Gary''s Student]

A simple function can only modify the cell into which it is entered.

To change several cells, create an array function and enter it as an array
in the worksheet:

Function arySample() As Variant
arySample = Array(1, 2, 3)
End Function


and in the worksheet, highlight A1,B1,C1 and
=arySample()
and enter with CNTRL-SHFT-ENTER rather than just the ENTER key.

 

[PAT]

I guess it's too complicated for me to do. I'm a beginner in macros!Thanks
anyway.Maybe I should post again in ' Excel general questions' .
Pat

 

[JLGWhiz]

It sounds like you might be able to use a Worksheet change event. Put this
code in the worksheet code module of the sheet receiving the data stream.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = Range("C1").Value Then
Beep
Beep
MsgBox "Reset Me"
End If
End Sub

The code will fire everytime the sheet receives data from the source, but
only when the two values are equal will it beep and put the message box up.

 

[PAT]

Thanks for your help.
I have found some help from the discussion but it does not seem to work for me

Playing a Sound Based on a Cell's Value
You might want to hear a sound when the value in a particular cell exceeds a
certain value. You can implement with a custom worksheet function that uses a
Windows API function.
BEGINNING OF COPIED TEXT
The Alarm function
Copy the code below to a VBA module in your workbook.
'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long


Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
NOTE: The Alarm function expects a WAV file (named sound.wav) in the same
path as the workbook. You will need to change this statement to match the
name (and path) of your actual sound file. If the sound file is not found,
the default system sound will be used.

Using the Alarm function in a formula
The Alarm function monitors a cell for a specified condition. If the
condition is met, the sound file is played and the function returns TRUE. If
the condition is not met, the sound file is not played and the function
returns FALSE. The Alarm function takes two arguments:
• Cell: A reference to a single cell (the cell that you are monitoring).
Normally, this will be a cell that contains a formula (but that is not
required).
• Condition: A text string that describes the condition
Following are examples of formulas that use this function:
=Alarm(A1,">=1000")
The sound will play when the value in cell A1 is greater than or equal to
1,000.
=Alarm(C12,"<0")
The sound will play when the value in cell C12 is negative.

Tips
• The function is evaluated whenever any cell that depends on the reference
cell is changed. The sound can get annoying!
• Normally, you will want to use this function in only one cell. If you use
it in more than one cell, you will not be able to tell which instance of the
function triggered the sound. END OF COPIED TEXT

I copied it to a module (nort a code). I placed the formula '
=Alarm(A1,">=1000") 'on the sheet in any cell but it does not even evaluate
it - it returns #value.Where am I supposed to place this formula? Do you have
any suggestion please?
Pat