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!

 

[Firefly2012]

What cell is the count formula in? I take it this formula is not part of the querytable (so it is an Excel formula as opposed to a count executed within the SQL)?

Where is the wav file located?

 

[Jon von der Heyden]

Welcome to the board!

I am a fan of using the native methods that Excel exposes.

So to play a beep sound, call Beep

From VBA help:

Beep Statement

<tbody>
</tbody>

Sounds a tone through the computer's speaker.
Syntax
Beep
Remarks
The frequency and duration of the beep depend on your hardware and system software, and vary among computers.
Example

This example uses the Beep statement to sound three consecutive tones through the computer's speaker.

<code> Dim I For I = 1 To 3 ' Loop 3 times. Beep ' Sound a tone. Next I </code>

<tbody>
</tbody>

You can also use speech to speak a word or phrase.. E.g:

Call Application.Speech.Speak("Dude, you have a new order!")

 

[ExcelFailure]

Thanks for the quick replies!

Yes, the count formula has nothing to do with the actual SQL or query and it's not part of the table. The file path for the .wav is currently C:\TRUMPETS

Thankyou

 

[Jon von der Heyden]

Are you using the worksheet calculate event?

 

[ExcelFailure]

Errm well I'm not using anything at the moment because I couldn't find anything that worked, but I'm sure there is.

I'm happy to try anything though

 

[Jon von der Heyden]

Ok. Sounds to me like you don't know hot to trap an event and sounds like should be using the worksheet change event. Stick this code in the sheet code module for the respective sheet.

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
        beep
        Call MsgBox(Prompt:="New order received", Buttons:=vbInformation + vbOKOnly)
    End If
End Sub

You need to m_strCountCellRef to refer to the count formula cell reference.

This should beep and display a message box each time the count value increases.

If you are determined to play a particular wav file then I suggest you use google and search for "play wav sound with vba". There are plenty are examples of code examples that you can include. May be best to contain that code in it's own module and procedure and then call it from this code that I have supplied; e.g. instead of beep and msgbox:

    Call PlaySound

 

[ExcelFailure]

Hi,

How do I refer the macro to the Count formula cell reference? And also, if I wanted speech instead of the beep, what would I change?

Many thanks

 

[Jon von der Heyden]

How do I refer the macro to the Count formula cell reference?

You need to m_strCountCellRef to refer to the count formula cell reference.

That would be this line (2):

Private Const m_strCountCellRef As String = "A1"

And also, if I wanted speech instead of the beep, what would I change?

If rng.Value2 > Evaluate(nme.RefersTo) Then
     'beep
     Call Application.Speech.Speak("Dude, you have a new order!")
     Call MsgBox(Prompt:="New order received", Buttons:=vbInformation + vbOKOnly)
End If

 

[ExcelFailure]

Hi,

This macro works, I've taken the message box out and added the voice speech thing. But it just loops the "Dude, you have a new order!" all the time. Can you help?

Thanks.