add a sound if Cell R6 value greater than 120

[Guest]

Hi, Does anyone know if I can add a .wav file if a cell value is greater than
120? If so can you offer any advice?

 

[Chip Pearson]

You need to use a VBA Event procedure. Right-click the sheet tab
and choose View Code. In the code module that appears on the VBA
Editor, paste the following code:

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
sndPlaySound32 "chimes.wav", 0
End If
End Sub

Change the reference to A1 to the cell you want to test, and
change "chimes.wav" to the sound file you wish to play. If the
file is not one of the standard Windows sound files, you'll need
to include the complete folder name of the file.


Cordially,
Chip Pearson
Pearson Software Consulting, LLC
www.cpearson

 

[Guest]

Thank you Chip Pearson

It works but the sound plays regardless of the number entered. I changed it
to <120 but it still plays the sound even at 1 or 200?

 

[Dave Peterson]

Chip has two routines that produce the chimes sound.

The worksheet_calculate routine would be used if the cell to check contained a
formula.

If you are changing the value in that cell by typing, you could modify the
second routine so that it checks to see what was entered. (I still used 10 as
the cutoff.)

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

If the cell contains a formula, then I'd delete the worksheet_change event code.

If the cell changes because of typing, I'd delete the worksheet_calculate event
code.

If it could be either, then keep both.

 

[Guest]

Thank you it works great!

Chip has two routines that produce the chimes sound.

The worksheet_calculate routine would be used if the cell to check contained a
formula.

If you are changing the value in that cell by typing, you could modify the
second routine so that it checks to see what was entered. (I still used 10 as
the cutoff.)

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

If the cell contains a formula, then I'd delete the worksheet_change event code.

If the cell changes because of typing, I'd delete the worksheet_calculate event
code.

If it could be either, then keep both.