Hello all,

A few days ago I asked for help playing a sound based on a
cell value. I was pointed to John Walkenbach's site that
did just that. In my case it needs tweaked a bit. It
plays over and over- I believe due to the fact the sheet
is continuously updating. I only need it to play once and
not play again until the cell value goes back to 0 and
starts up again. Here's what I have:

'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 & "\go.wav" 'Edit this
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

Function =Alarm(D2,"0")

I want it to play "go.wav" "once" when it is greater than
zero. The cell D2 represents a manufacturing line speed. I
want to know went it starts. My sheet refreshes every 2
secs pulling from a server which is fed by a PLC. After
it starts and gets up to speed at about 600 or so, it
fluctuates on almost every refresh. This causes the sound
file to play over and over. Any help would be appreciated.

Thanks, Mike

-----Original Message-----
Hello all,

A few days ago I asked for help playing a sound based on
a
cell value. I was pointed to John Walkenbach's site that
did just that. In my case it needs tweaked a bit. It
plays over and over- I believe due to the fact the sheet
is continuously updating. I only need it to play once and
not play again until the cell value goes back to 0 and
starts up again. Here's what I have:

'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 & "\go.wav" 'Edit
this
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

Function =Alarm(D2,"0")

I want it to play "go.wav" "once" when it is greater than
zero. The cell D2 represents a manufacturing line speed.
I
want to know went it starts. My sheet refreshes every 2
secs pulling from a server which is fed by a PLC. After
it starts and gets up to speed at about 600 or so, it
fluctuates on almost every refresh. This causes the sound
file to play over and over. Any help would be
appreciated.

Thanks, Mike

I thought about it some more and a simple answer came to
me. I'll just set a second cell value to 1 if D20 and
read the function off the second cell. Then the
fluctuations won't cause the sound file to repeat. Duh!


Mike

It's not the fluctuations that cause the replay it's the
sheet refresh. Back to square one.

Mike