Nearly given up on you<vbg.

Two questions,

How is the hour and minutes in A1 and B1 being updated, as it seems to me
that the formula will only work if those cells re-calculate at the correct
moment.

How do you think the alarm code is invoked, that is what in your formula is
forcing it, as I can't see it in your response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"___Zoom" wrote in message
...
Bob,
Thanks for your reply. Here is my problem with some detail:
I am displaying my system clocks hour on Sheet1 A1 and minute on Sheet1
B1.
Using these figures with some criteria I have created IF.. statements to
display certain messages for some cells. An example is:

=IF(AND(A1=7;B1=55);"start lesson 1";IF(AND(A1=8;B1=35);"start lesson
2";IF(AND(A1=9;B1=15);"start lesson 3";IF(AND(A1=10;B1=10);"start lesson
4";IF(AND(A1=10;B1=50);"start lesson 5";IF(AND(A1=11;B1=40);"start lesson
6";IF(AND(A1=12;B1=20);"start lesson 7";"--")))))))

All works fine. But I wanted to add sound alerts for the same criteria
too.
I've tried the macro examples given as references to previous work (with
alterations to suit my case of course):
================================================== ==
'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long


Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
===========================================
AND
===========================================
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 = "dogbark.wav"
WAVFile = ThisWorkbook.Path & "\" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub
====================================
but failed to make it work. Don't know what I am doing wrong. I'd
appreciate
if anyone could help.
Zoom


"Bob Phillips" wrote in message
...
Zoom,

I just created this simple worksheet change event code and it worked
okay

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "A1" Then
Alarm Target, "100"
End If

End Sub

I copied John's Alarm Fun ction into a normal code module, this code in
the
sheet module, and change the sound file as suggested by John.

What are you doing?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)