Re: Macro Help

Tech-Archive recommends: Speed Up your PC by fixing your registry


Greg:
I replaced my code with yours and got an error when I changed the value of the cell that calls the sound. Here is a screen capture that shows the error and the whole macro. I added nothing else except the code in the excel ***.
=Alarm(H8,"<k13")



You are almost correct in your assumption. Right now when the value goes above or below the number it keeps playing the sound over and over. I believe because the cell keeps getting updated every second by the futures program.
I wanted to set up two cells one for > and one for < I only want it to play the sound one time when the one cell is > and one time when the other cell is <


Larry

Greg Wilson wrote:
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


"notme" wrote:

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 wrote:
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


"justice" wrote:

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

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


JPEG image

Quantcast