Macro Help

[justice]

I have a excel program that I want to play a sound if a cell goes above
what I set the value to. I found this Macro that works but it continues
to play the sound after the conditions are met. How can I make it play
the sound only once?

Thank you

******************************************

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"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True

Exit Function
End If
ErrHandler:
Alarm = False
End Function

********************************************************

 

[justice]

I would also like to play a different sound if the cell goes below the
value. I know nothing about VB macro programming.

thank you

 

[Greg Wilson]

Perhaps this where WFile_1 and WFile_2 are the paths to the desired files:

Dim Above As Boolean
Const WFile_1 As String = "C:\WINNT\Media\Ringin.Wav"
Const WFile_2 As String = "C:\WINNT\Media\Ringout.Wav"

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Function Alarm(Cell, Condition)
On Error GoTo ErrHandler
If Above = False And Evaluate(Cell.Value & Condition) Then
Above = True
Call PlaySound(WFile_1, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
ElseIf Above = True And Not Evaluate(Cell.Value & Condition) Then
Above = False
Call PlaySound(WFile_2, 0&, SND_ASYNC Or SND_FILENAME)
End If
Exit Function
ErrHandler:
Alarm = False
End Function

Regards,
Greg

 

[notme]

Greg:
It looks like what I want but pardon me for being so stupid. How do I
incorporate this code into the macro?

I understand that you made the directory where the sound files reside
"C:\WINNT\Media\Ringin.Wav" I can change that and make and label the
sound files.
But what part of this code goes in what I have and where? Could you cut
and past in what part I need?

thank you

Larry


..

 

[Greg Wilson]

Larry,

Strictly speaking, it's a user defined function (UDF) and not a macro.

What I inferred is that you have a cell or cells that contain the Alarm
function and you want Excel to make a specific sound when the value in a cell
referenced by the function first exceeds a given number (i.e. only make the
sound at first crossover) and to make another sound when it first goes below
the same number (again only on first crossover). For example, if cell C1
contains the following:

=Alarm(A1, ">100")

Then if the value in A1 is changed from, say 95 to 101, you want Excel to
make a sound. However, you don't want the sound to repeat if and when the
value is subsequently changed to another number that is also greater than
100. Similarly, if and when the value in A1 changes to a value below 100 then
you want to hear a different sound but not to repeat if A1 is subsequently
changed to another number also below 100.

My code pasted as is (supplanting yours) should do this provided the
specified paths in fact reference wave files on your system. I had intended
that you just make the substitution. Note that you didn't post the PlaySound
API function but you must have it declared somewhere in order for your code
to work (unless I'm missing something). You shouldn't list this more than
once. In case you're confused, the following must be declared in a module
somewhere in your project unless I'm missing something. Don't list it more
than once:

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Final note: Don't delete your current code unless and until mine has been
clearly proven to be what you want. You can deactivate your existing code by
simply putting a single apostrophe in front of each line (it should turn
green). The IDE will then treat it as if it is only comment text and will
ignore it.

Regards,
Greg