Sound Triggering Problem

[Ditchy]

Greetings one and all,

Ok, so I have a range of cells, in Sheet2 - A1:E6 which = a range from Sheet1 (formula's are calculating values). If any of those values result in a "1", its set to play a sound. However, those values are left in that cell and if there are multiple cells the sound is being triggered multiple times upon each execution. Any Ideas on how to get the code to ignore the cells who's value already contains "1" or that have already triggered the sound?

Its the same sound being triggered ....

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


Private Sub Worksheet_Calculate()
Const FNameWin As String = "D:\sounds\Ding48"
If Range("A1").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("A2").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("A3").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("A4").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("A5").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("B1").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("B2").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("B3").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("B4").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("B5").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("C1").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("C2").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("C3").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("C4").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("C5").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("D1").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("D2").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("D3").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("D4").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("D5").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("E1").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("E2").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("E3").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("E4").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
If Range("E5").Value = "1" Then
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
End If
End Sub

Thanks!

 

[portews]

Instead of triggering the sounds per cell, as you have now, count the number of 1's in the range and store it as a global variable. When the count changes, sound the sound.

Put this at the top of a Module to dimension countbasis as a global variable to store it between calculations.

Global countbasis As Integer

Put this in the Sheet 2 code page

Private Sub Worksheet_Calculate()

[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/961328-sound-triggering-problem.html[/COLOR]
Dim ones As Integer
[COLOR=#00ff00]'count the number of 1's in the range[/COLOR]
ones = Application.WorksheetFunction.CountIf(Range("A1:E6"), "1")
[COLOR=#00ff00]'if the current count of 1's is different from the previous number of 1's[/COLOR]
If Not ones = countbasis Then
    [COLOR=#00ff00]'play the sound[/COLOR]
    Call PlaySound(FNameWin, 0&, SND_ASYNC Or SND_FILENAME)
    [COLOR=#00ff00]'reset the countbasis to the latest count for the next comparison[/COLOR]
    countbasis = ones
End If
End Sub

 

[Ditchy]

Hi Bill, thanks for this. I actually realised I could simplify it right after I posted, however, your version is even better. Thanks!!