Looping and adding sound

[Help for Heroes]

Hi, I'm trying to add a .wav sound to all cells in a column if it equals "! ALERT !". I have created a macro and added the following code:

Private DeclareFunction PlaySound Lib "winmm.dll" _<o></o>
Alias "PlaySoundA" (ByVal lpszNameAs String, _<o></o>
ByVal hModule As Long, ByVal dwFlags As Long)As Long<o></o>
<o> </o>
Const SND_SYNC = &H0<o></o>
Const SND_ASYNC = &H1<o></o>
Const SND_FILENAME = &H20000<o></o>
<o></o>
Sub PlayWAV()<o></o>
WAVFile = ThisWorkbook.Path &"\Windows Exclamation.wav"<o></o>
Call PlaySound(WAVFile, 0&, SND_ASYNCOr SND_FILENAME)<o></o>
End Sub<o></o>
<o> </o>
<o> </o>
Private SubWorksheet_Change(ByVal Target As Range)<o></o>
<o> </o>
Threshold = "! ALERT !"<o></o>
If Range("AZ4").Value = ThresholdThen PlayWAV<o></o>
<o> </o>
End Sub<o></o>



This works for the first cell in the column and I'm assuming I'll to add a loop within it to apply the same to the rest of the column cells. I would also like to reference .wav files in other files but using c:\BERT\BEAM-ME-UP-SCOTTY doesn't work? How do I do this and is it something to do with the FILEPATH? This is my first macro and I would appreciate help in achieving this, please help. Many thanks J

 

[JLGWhiz]

You don't need a loop if you are manually selecting the cells in column AZ.
I believe something like this would work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, rng As Range, c As Range
lr = ActiveSheet.Cells(Rows.Count, "AZ").End(xlUp).Row
Set rng = ActiveSheet.Range("AZ4:AZ" & lr)
Set c = Intersect(Target, rng)
Threshold = "! ALERT !"
If c.Value = Threshold Then PlayWAV
End Sub

This would replace the current worksheet_change code

 

[JLGWhiz]

To use the sound system in other worksheets, you need to make some of the code public instead of private. Put this at the top of code module1.

Public DeclareFunction PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszNameAs String, _
ByVal hModule As Long, ByVal dwFlags As Long)As Long
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Then for each .wav file you call you will need to revise this by change the parts highlighted in red.

Sub [COLOR=#800000]PlayWAV[/COLOR]()
WAVFile = [COLOR=#800000]ThisWorkbook.Path &"\Windows Exclamation.wav"
[/COLOR]Call PlaySound(WAVFile, 0&, SND_ASYNCOr SND_FILENAME)
End Sub

 

[JLGWhiz]

I should also mention that the Play sub goes into the standard module 1. But, your calling code can go anywhere to call the various Play subs.