add a sound if cell A1>80, A2>90, B1>100, etc.....

[Astroboy]

Hi Everyone,

Very Newbie here..

I was searching board and found bottom code and it works on ONE cell.

Q1) But I want to have Sound Alarm for Mutiple cells:

When
A1>=80
A2>=90
B2>=80
R5>=100
....etc..

Q2) And when from Cell B1 to B17 and if any cell value is >= 100

Q3) How can I make sound to run about 10 seconds?

Thank you in advance....

Option Explicit
Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

Private Sub Worksheet_Calculate()
If Me.Range("A1").Value > 10 Then
sndPlaySound32 "chimes.wav", 0
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value > 10 Then
sndPlaySound32 "chimes.wav", 0
End If
End If
End Sub

 

[Conan Kelly]

Astroboy,

Q3) How can I make sound to run about 10 seconds?

You can't make this sound play longer than 10 seconds. You have a couple
options. You could put multiple play sound commands in the code:

If TRUE Then
If True Then
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
End If
End If

(I haven't tested this. I don't know if the sounds will overlap each other)

Or you can pick 1 sound that is 10 seconds in length. Or you can use
Windows Sound Recorder (%SystemRoot%\System32\sndrec32.exe) to edit this
sound (make a new file) so that it plays multiple times. I don't remember
if Sound Recorder has the tools/ability to copy the whole sound and paste
multiple instances at the end of the current sound recording. You'll have
to experiment with it. If Sound Recorder won't do it, I'm sure you can find
several free programs online that will allow you to do that.

HTH,

Conan

 

[Conan Kelly]

Astroboy,

Q1) But I want to have Sound Alarm for Mutiple cells:

When
A1>=80
A2>=90
B2>=80
R5>=100
...etc..

Q2) And when from Cell B1 to B17 and if any cell value is >= 100

I'm thinking the easiest way to do this might be to use a helper cell that
is hidden or out of the way. I'm gonna use cell C1 in this example, you can
choose which ever cell makes sense to you.

Use this formula in Cell C1:

=OR(A1>=80,A2>=90,B2>=80,R5>=100,...etc...,COUNTIF(B1:B17,">=100"))

Then in your code, you could do something like this:

If Range("C1") Then
sndPlaySound32 "chimes.wav", 0
End If


HTH,

Conan

 

[Astroboy]

Oh.... I can do that.... and THANK YOU~~!!

Astroboy,


You can't make this sound play longer than 10 seconds. You have a couple
options. You could put multiple play sound commands in the code:

If TRUE Then
If True Then
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
sndPlaySound32 "chimes.wav", 0
End If
End If

(I haven't tested this. I don't know if the sounds will overlap each other)

Or you can pick 1 sound that is 10 seconds in length. Or you can use
Windows Sound Recorder (%SystemRoot%\System32\sndrec32.exe) to edit this
sound (make a new file) so that it plays multiple times. I don't remember
if Sound Recorder has the tools/ability to copy the whole sound and paste
multiple instances at the end of the current sound recording. You'll have
to experiment with it. If Sound Recorder won't do it, I'm sure you can find
several free programs online that will allow you to do that.

HTH,

Conan

 

[Astroboy]

Hi Conan,

Thank you for helping me out....