I have been trying to set up a spdsht that will compare the previous
10 day volume with the current volume. this is compared by 30 minute
increments. for example i am comparing the volume traded from 10am-
10:30am EST with the previous 10 Days at the same time period (10-
10:30am).
What i have is an alarm that goes off when todays volume exceeds the
prev 10 day average. i want to be able to have is :

1. the alarm sounds when todays volume exceeds prev 10 Day
2. Have a "stop sound" button or "reset" button that stops
the alarm for that 30 Min period, but will continue to
update after the 30 interval is complete.


This is the code that i have so far in my module:
///////////////////////////////////////////////////////
'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 & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
alarm = True
Exit Function
End If

ErrHandler:
alarm = False
End Function
/////////////////////////////////////////////////////////
Public Sub SoundBeep()
If Range("I7") = "True" Then
ActiveCell.Range("I7") = "=IF(RC[-3]RC[-1],alarm(RC[-
3],""H7"")"
Else
If Range("I7") = "False" Then
alarm "I7", "=0"
End If

End If
End Sub
////////////////////////////////////////////////////////////

This is the code i have in Sheet 3

Dim intResult As Integer

Private Sub CommandButton01_Click()
intResult = PlaySound(vbNullString, vbNull, SND_NODEFAULT)

End Sub
///////////////////////////////////////////////////////////

If anyone would like better clarification on any specifics please
contact me or message me. I would really appreciate some help from
those that really enjoy the nitty gitty about XL.
Thanks very much

--
Message posted from http://www.ExcelForum.com

Hi
one question regarding your 'stop button' where should this button be:
- should you get a messagebox if the alarm was issued and you can
decide whether to stop or not (this will work on individual cell base)
- do you want a global 'Stop button'?

For the first one you may try the following: Replace the alarm function
with the following adapted version:

Function alarm(Cell, Condition)
Static cond(30, 30)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Dim ret_value
On Error GoTo ErrHandler

If Evaluate(Cell.Value & Condition) Then
If cond(Cell.row, Cell.Column) < Now Or cond(Cell.row, Cell.Column)
= "" Then
cond(Cell.row, Cell.Column) = ""
WAVFile = ThisWorkbook.path & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

ret_value = MsgBox("Alarm - Stop for 30 minutes?", vbYesNo)
If ret_value = 6 Then
cond(Cell.row, Cell.Column) = Now + TimeValue("00:30:00")
End If
alarm = True
Exit Function
Else
' do nothing
End If
End If

ErrHandler:
alarm = False
End Function

------
Note: I declared a static array with the a dimension of 30 rows/30
columns. You may have to adapt this if you have your alarm cell for
example in A100



--
Regards
Frank Kabel
Frankfurt, Germany

I have been trying to set up a spdsht that will compare the previous
10 day volume with the current volume. this is compared by 30 minute
increments. for example i am comparing the volume traded from 10am-
10:30am EST with the previous 10 Days at the same time period (10-
10:30am).
What i have is an alarm that goes off when todays volume exceeds the
prev 10 day average. i want to be able to have is :

1. the alarm sounds when todays volume exceeds prev 10 Day
2. Have a "stop sound" button or "reset" button that stops
the alarm for that 30 Min period, but will continue to
update after the 30 interval is complete.


This is the code that i have so far in my module:
///////////////////////////////////////////////////////
'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 & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
alarm = True
Exit Function
End If

ErrHandler:
alarm = False
End Function
/////////////////////////////////////////////////////////
Public Sub SoundBeep()
If Range("I7") = "True" Then
ActiveCell.Range("I7") = "=IF(RC[-3]RC[-1],alarm(RC[-
3],""H7"")"
Else
If Range("I7") = "False" Then
alarm "I7", "=0"
End If

End If
End Sub
////////////////////////////////////////////////////////////

This is the code i have in Sheet 3

Dim intResult As Integer

Private Sub CommandButton01_Click()
intResult = PlaySound(vbNullString, vbNull, SND_NODEFAULT)

End Sub
///////////////////////////////////////////////////////////

If anyone would like better clarification on any specifics please
contact me or message me. I would really appreciate some help from
those that really enjoy the nitty gitty about XL.
Thanks very much.


---
Message posted from http://www.ExcelForum.com/