playing sounds once only

[board]

hi there,
I have 3 cells containing data from the outside world for 3 machines, when the machines are running the cell value in lets say a1 would equal 1, b1 would equal 1 and c1 would equal 1. When any machine stops its value changes to 0. I want to play a voice sound for each machine being off. (if a1=0 then say "machine 1 off" etc). My problem is I only want the voice file to play once. When a second machine stops I don't get what I require.

using the playsound code below and calling the sound from the worksheet_calculate


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

Sub PlayWAVmachine1()
'play shuttle alarm sound a1 value=0
WAVFile = "machine1.wav"
WAVFile = ThisWorkbook.Path & "" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Sub PlayWAVmachine2()
'play shuttle alarm sound b1 value =0
WAVFile = "machine2.wav"
WAVFile = ThisWorkbook.Path & "" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Sub PlayWAVmachine3()
'play shuttle alarm sound c1 value=0
WAVFile = "machine3.wav"
WAVFile = ThisWorkbook.Path & "" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

cheers
Stuart

 

[ask2tsp]

My problem is I only want the voice file to play once.

Stuart I don't quite understand what you mean by that.

I optimized your code as follows

In a general module

Option Explicit

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

Sub PlayWAV(WAVFile As String)
   Call PlaySound(ThisWorkbook.Path & "\" & WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

and in the sheet's code page

Option Explicit

Private Sub Worksheet_Calculate()
   If Range("A1") = 0 Then PlayWAV "machine1"
   If Range("B1") = 0 Then PlayWAV "machine2"
   If Range("C1") = 0 Then PlayWAV "machine3"
End Sub

Now every time one of the cells A1, B1 or C1 changes to 0 the corresponding wav is played.

 

[board]

the sheet recalculates every few seconds so I believe that because the cells may not have changed the sounds are triggered again. I only want the alarm to sound once every time machine stops.

 

[ask2tsp]

Ok now I see what you mean. The following code flags when a cel becomes zero. Now the sound only plays once while the cel=0

Private Sub Worksheet_Calculate()
   Static zeroState1 As Boolean
   Static zeroState2 As Boolean
   Static zeroState3 As Boolean
   
   If [A1] = 0 And Not zeroState1 Then
      PlayWAV "machine1"
      zeroState1 = True
   ElseIf [A1] <> 0 Then
      zeroState1 = False
   End If
   
   If [B1] = 0 And Not zeroState2 Then
      PlayWAV "machine2"
      zeroState2 = True
   ElseIf [B1] <> 0 Then
      zeroState2 = False
   End If
   
   If [C1] = 0 And Not zeroState3 Then
      PlayWAV "machine3"
      zeroState3 = True
   ElseIf [C1] <> 0 Then
      zeroState3 = False
   End If
   
End Sub

 

[board]

that's looking a lot more promising. I'll have a go this afternoon and get back to you. Thanks for your help so far.

Stuart

 

[board]

Many thanks. Working.
Stuart