I have a DDE link to an Excel spreadsheet, and would like to be notified by
an alert (preferably music) when a specific formula result occurs.

Any solutions greatly appreciated.

Hi,

have a look here
http://www.j-walk.com/ss/excel/tips/tip59.htm
Mike

"Bobh" wrote:

I have a DDE link to an Excel spreadsheet, and would like to be notified by
an alert (preferably music) when a specific formula result occurs.

Any solutions greatly appreciated.

The link that Mike H posted has good solution - I just borrowed some of that
to provide audio ability in a file to one of my clients.

In your case you could use the Worksheet_Calculate() event handler for the
sheet with the formula you're interested in to examine the result of the
formula and if it's what you're looking for, then play the audio clip. Code
would look similar to this (it includes the code from John Walkenbach's site
page):

Option Explicit
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()
Dim WAVFile As String
WAVFile = "dogbark.wav"
WAVFile = ThisWorkbook.Path & "\" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Private Sub Worksheet_Calculate()
Const cellAddress = "$C$1" ' change
Const notifyValue = 7 ' value to alert on

If Range(cellAddress) = notifyValue Then
PlayWAV ' audio alert
End If
End Sub

That would all go into the sheet's code module - to get it there,
right-click on the sheet's name tab and choose [View Code] and cut and paste
the code into it and change the two const values cellAddress and notifyValue
as required.


"Bobh" wrote:

I have a DDE link to an Excel spreadsheet, and would like to be notified by
an alert (preferably music) when a specific formula result occurs.

Any solutions greatly appreciated.

Better code for the Worksheet_Calculate() event. This will keep the audio
from playing every time the sheet is recalculated and the test value is at
the point you're interested in. Instead it will play the first time it
changes to the test value and then keep quiet unless the value changes to
something else and then back to the test value later.

Private Sub Worksheet_Calculate()
Const cellAddress = "$C$1" ' change
Const notifyValue = 7 ' value to alert on
Static AlreadyPlayed As Boolean

If Range(cellAddress) = notifyValue And _
Not AlreadyPlayed Then
PlayWAV
AlreadyPlayed = True
Else
AlreadyPlayed = False
End If
End Sub


"JLatham" wrote:

The link that Mike H posted has good solution - I just borrowed some of that
to provide audio ability in a file to one of my clients.

In your case you could use the Worksheet_Calculate() event handler for the
sheet with the formula you're interested in to examine the result of the
formula and if it's what you're looking for, then play the audio clip. Code
would look similar to this (it includes the code from John Walkenbach's site
page):

Option Explicit
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()
Dim WAVFile As String
WAVFile = "dogbark.wav"
WAVFile = ThisWorkbook.Path & "\" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Private Sub Worksheet_Calculate()
Const cellAddress = "$C$1" ' change
Const notifyValue = 7 ' value to alert on

If Range(cellAddress) = notifyValue Then
PlayWAV ' audio alert
End If
End Sub

That would all go into the sheet's code module - to get it there,
right-click on the sheet's name tab and choose [View Code] and cut and paste
the code into it and change the two const values cellAddress and notifyValue
as required.


"Bobh" wrote:

I have a DDE link to an Excel spreadsheet, and would like to be notified by
an alert (preferably music) when a specific formula result occurs.

Any solutions greatly appreciated.