How do you play a wav file when a cell value changes?

[dwebb03]

How can i trigger this code when Cell A1 changes to a value greater 77, or is there another way this is all I could find while searching. Thanx in advance


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

Sub PlayWAV()
WAVFile = "hal.wav"
WAVFile = ThisWorkbook.Path & "\" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

 

[Harvey]

check out the worksheet_change function.

an example':

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "A1" And Target.Value > 77 Then
        MsgBox "testtest"
        Beep
        PlayWAV
    End If

End Sub

 

[dwebb03]

Question

Where do I put this portion of code I have the other portion in the thisworkbook

 

[Harvey]

you should put it in the sheet where 'A1' is found (ie. sheet1)

 

[erik.van.geit]

Hi,

if I might comment a bit:
If Target.Address = "A1" will not work
Address is returning "$A$1" (absolute reference)
you can use
If Target.Address = "$A$1"
or
If Target.Address(0, 0) = "A1" (relative reference)

see helpfiles for "Address"

kind regards,
Erik

 

[dwebb03]

Help Please

It's coming up with sub or function not described when it comes to PlayWAV Do I have the first portion of code correct and in the right sheet Thank you.

 

[erik.van.geit]

playWAV must be in a normal module

On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

 

[dwebb03]

A little more help please

Thanx Erik I'm Getting closer but it only works when the "A1" value is above 77 and I click the cell. Can you help me so that when ever the value is above 77 it plays the wav automatically perhaps repeating if I'm not asking too much. Thank You I'm quite new at this as you can tell.

 

[Harvey]

is A1 a formula?
if that is the case you have to change worksheet_change into worksheet_calculate

 

[dwebb03]

Thanx still trying

yes eventually but I was just trying to learn how to get it to work with just changing the value in the cell manually. I changed it to calculate but that doesn't seem to work.