Solved: Make a Sound When a Cell is Changed to a Certain Value

[VA_Voter]

This is the same title as a post by DRJ.

When I enter the code in an open spreadsheet it works fine, however, after saving, exiting and then reloading it doesn't work.

I'm using Excel 2003 with W7.

Thanks,

[VA_Voter]

Here's the link to the DRJ post:

www dot vbaexpress dot com / kb / getarticle.php?kb id=161

[Jacob Hilderbrand]

Are you enabling macros? If you are not prompted when opening the workbook go to Tools | Macros | Security and set it to Medium.

[VA_Voter]

Thanks, that did it. Solved.

[jixy]

i can get the code to work,but after i enter a value of 10 or more in the selected range, i get a ding in other cells no matter what the value is that i entered

[Teeroy]

Hi Jixy,

It's better to start a new post with a new problem, especial as the OP has marked it solved. But to answer your question it will ding because any time you make a change anywhere it checks the selected range and finds a value greater than 10. If you only want a ding when you change a value to greater than 10 in that range you need to remove the loop and tweak the IF like so:

[vba]Option Explicit

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

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("D5:G25")
If Not Intersect(Target, CheckRange) Is Nothing And Target.Value > 10 Then
PlaySound = True
End If
If PlaySound Then
Call sndPlaySound32("C:\windows\media\ding.wav", 1)
End If

End Sub[/vba]

[jixy]

I am so impressed and grateful that you took the time to respond to my inquiry. I'm still having problems pasting it into the "view code" tab of excel as only the last line of code shows up, but i'll try to figure that out. thanks for the code and the advice on posting...sincerely,jixy