MS Excel play a sound macro question

[ExcelFailure]

Hi,

Im working on Excel 2007 and I have a table of our sales orders that updates automatically on a Microsoft Query. From this I've done a Count formula to tell me how many orders there are for today.

What is was wanting to do is have a sound play, preferably a .wav whenever a new order comes in, or in effect, when the cell with the Count formula in changes. I've tried loads of various macros but they don't seem to work. I may be doing something wrong..can anybody help??

Thanks a lot!

 

[Jon von der Heyden]

I forgot to set the new value;

Private Const m_strPriorCountName As String = "str_PriorCount"
Private Const m_strCountCellRef As String = "A1"

Private Sub Worksheet_Calculate()
    Dim nme As Excel.Name
    Dim rng As Excel.Range

    Set rng = Me.Range(m_strCountCellRef)

    On Error Resume Next
        Set nme = ThisWorkbook.Names(m_strPriorCountName)
    On Error GoTo 0

    If nme Is Nothing Then
        Call ThisWorkbook.Names.Add(Name:=m_strPriorCountName, RefersTo:=rng.Value2, Visible:=False)
        Exit Sub
    End If

    If rng.Value2 > Evaluate(nme.RefersTo) Then
        Call Application.Speech.Speak("Dude, you have a new order!")
    End If
    
    nme.RefersTo = rng.Value2
End Sub