Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
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 ************************************************** ****** |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
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 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 ************************************************** ****** |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
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 ************************************************** ****** |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
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 ************************************************** ****** |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Help
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 ************************************************** ****** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |