I am having difficulties on implementing Excel sound alert function to
an XL application of mine. In my problem, I want to trigger the sound file
depending on the content of two cells (Sheet1's A1 and B1: which should have
10 suitable pairs for triggering). Furthermore, I want to bound the process
of checking these cells content to a automatic check of system time of my
PC. The system clocked should be checked every munite and if the conditions
are met for Sheet1 A1 and Sheet1 B1 then the sound file be played. Can
anyone help me with this please?
I can get into more detail if any group member is interested...TIA

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)

"___Zoom" wrote in message
...
I am having difficulties on implementing Excel sound alert function to
an XL application of mine. In my problem, I want to trigger the sound file
depending on the content of two cells (Sheet1's A1 and B1: which should
have
10 suitable pairs for triggering). Furthermore, I want to bound the
process
of checking these cells content to a automatic check of system time of my
PC. The system clocked should be checked every munite and if the
conditions
are met for Sheet1 A1 and Sheet1 B1 then the sound file be played. Can
anyone help me with this please?
I can get into more detail if any group member is interested...TIA

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)

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)

Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this line from
the original code because I thought that I could do the same control with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two cells (A1
& B1). Couse the original statement format looks like it only accepts "a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location of my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8 And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1 = 9 And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or (Sheet1!A1 = 10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or (Sheet1!A1 =
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And (Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal line like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the "Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()" ?

Thanks for not giving up on me. I'll be waiting impatiantly for your
solution suggestions :)
TIA

"Bob Phillips" wrote in message
...
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)

Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this line from
the original code because I thought that I could do the same control with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two cells
(A1
& B1). Couse the original statement format looks like it only accepts "a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location of my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8 And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1 = 9 And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or (Sheet1!A1 = 10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or (Sheet1!A1 =
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the
"Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()" ?

Thanks for not giving up on me. I'll be waiting impatiantly for your
solution suggestions :)
TIA

"Bob Phillips" wrote in message
...
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)

Rob,
Thanks for your suggestions. Since I have P.M sessions as well for "start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1 for
controlling the criteria displayed in cells D3 and D4. Now there is a "dong"
sound (default windows alert sound) when the criteria is met. All seems to
be working OK, apart from not playing the desired *.wav file. My wav file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1, that is
all that I have changed from your suggestion codes. I have placed the Code
into the Module1 and Workbook code pages as well with no difference. I am
not getting any error messages while the macro is running or when the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this line
from
the original code because I thought that I could do the same control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two cells
(A1
& B1). Couse the original statement format looks like it only accepts "a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location of my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8 And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1 = 9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or (Sheet1!A1 =
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or (Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the
"Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()" ?

Thanks for not giving up on me. I'll be waiting impatiantly for your
solution suggestions :)
TIA

"Bob Phillips" wrote in message
...
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)

Bob,
Unfortunately replacing the code has no effect at all. As you may have
noticed from my previous reply to the thread, I have replaced the location &
name of the *.wav file in Rob's code...Thus although not giving any error
messages, the macro plays the ubiquitous dong -only- (not the wav file I
specify) until the minute figure changes for 5 sec. intervals. I think we
are missing something here. Hope someone can dig it out.
Sincerely
Zoom

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

Rob's code has a small but crucial error. It accepts a filename argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well for
"start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1 for
controlling the criteria displayed in cells D3 and D4. Now there is a
"dong"
sound (default windows alert sound) when the criteria is met. All seems
to
be working OK, apart from not playing the desired *.wav file. My wav
file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1, that
is
all that I have changed from your suggestion codes. I have placed the
Code
into the Module1 and Workbook code pages as well with no difference. I
am
not getting any error messages while the macro is running or when the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in
message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this line
from
the original code because I thought that I could do the same control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two
cells
(A1
& B1). Couse the original statement format looks like it only
accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location
of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8
And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1 =
9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or (Sheet1!A1
=
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC
Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal
line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the
"Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()" ?

Thanks for not giving up on me. I'll be waiting impatiantly for your
solution suggestions :)
TIA

"Bob Phillips" wrote in message
...
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)

OK Bob, I 'll post it right away.
Thanks
Zoom

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

It works for me (doesn't it always!).

Cut to the quick, why don't you send me your workbook that doesn't work
and
I will see if I can track it down.

My email address includes a spurious 'nothere' text, so be careful.

--

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,
Unfortunately replacing the code has no effect at all. As you may have
noticed from my previous reply to the thread, I have replaced the
location
&
name of the *.wav file in Rob's code...Thus although not giving any
error
messages, the macro plays the ubiquitous dong -only- (not the wav file I
specify) until the minute figure changes for 5 sec. intervals. I think
we
are missing something here. Hope someone can dig it out.
Sincerely
Zoom

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

Rob's code has a small but crucial error. It accepts a filename
argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If
this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well for
"start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1
for
controlling the criteria displayed in cells D3 and D4. Now there is
a
"dong"
sound (default windows alert sound) when the criteria is met. All
seems
to
be working OK, apart from not playing the desired *.wav file. My wav
file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1,
that
is
all that I have changed from your suggestion codes. I have placed
the
Code
into the Module1 and Workbook code pages as well with no difference.
I
am
not getting any error messages while the macro is running or when
the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in
message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias
"PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long)
As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this
line
from
the original code because I thought that I could do the same
control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for
two
cells
(A1
& B1). Couse the original statement format looks like it only
accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original
location
of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 =
8
And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or
(Sheet1!A1
=
9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or
(Sheet1!A1
=
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20)
And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&,
SND_ASYNC
Or
SND_FILENAME)

' The long statement above tries to call the alarm sound
function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an
additonal
line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into
the
"Private
Sub Nextzaman()" module rather then "Private Sub
Workbook_Open()"
?

Thanks for not giving up on me. I'll be waiting impatiantly for
your
solution suggestions :)
TIA

"Bob Phillips" wrote in
message
...
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)

Zoom,

Rob's code has a small but crucial error. It accepts a filename argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well for "start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1 for
controlling the criteria displayed in cells D3 and D4. Now there is a
"dong"
sound (default windows alert sound) when the criteria is met. All seems to
be working OK, apart from not playing the desired *.wav file. My wav file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1, that is
all that I have changed from your suggestion codes. I have placed the Code
into the Module1 and Workbook code pages as well with no difference. I am
not getting any error messages while the macro is running or when the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this line
from
the original code because I thought that I could do the same control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two
cells
(A1
& B1). Couse the original statement format looks like it only accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8 And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1 = 9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or (Sheet1!A1 =
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the
"Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()" ?

Thanks for not giving up on me. I'll be waiting impatiantly for your
solution suggestions :)
TIA

"Bob Phillips" wrote in message
...
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)

Got it all right. Thanks a lot...
Zoom

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

Response posted direct. Mail me if you don't get it.

Bob

"___Zoom" wrote in message
...
OK Bob, I 'll post it right away.
Thanks
Zoom

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

It works for me (doesn't it always!).

Cut to the quick, why don't you send me your workbook that doesn't
work
and
I will see if I can track it down.

My email address includes a spurious 'nothere' text, so be careful.

--

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,
Unfortunately replacing the code has no effect at all. As you may
have
noticed from my previous reply to the thread, I have replaced the
location
&
name of the *.wav file in Rob's code...Thus although not giving any
error
messages, the macro plays the ubiquitous dong -only- (not the wav
file
I
specify) until the minute figure changes for 5 sec. intervals. I
think
we
are missing something here. Hope someone can dig it out.
Sincerely
Zoom

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

Rob's code has a small but crucial error. It accepts a filename
argument,
but ignores it and tries to play a file called "C:\T\chimes.wav".
If
this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well
for
"start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and
K1
for
controlling the criteria displayed in cells D3 and D4. Now there
is
a
"dong"
sound (default windows alert sound) when the criteria is met.
All
seems
to
be working OK, apart from not playing the desired *.wav file. My
wav
file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and
E1,
that
is
all that I have changed from your suggestion codes. I have
placed
the
Code
into the Module1 and Workbook code pages as well with no
difference.
I
am
not getting any error messages while the macro is running or
when
the
criteria is met and "dong" sounds. Hope you may find the
reason...
Sincerely

"Rob van Gelder" wrote
in
message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:\0
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias
"PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As
Long)
As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute
intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed
this
line
from
the original code because I thought that I could do the same
control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work
for
two
cells
(A1
& B1). Couse the original statement format looks like it
only
accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original
location
of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or
(Sheet1!A1
=
8
And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or
(Sheet1!A1
=
9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or
(Sheet1!A1
=
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30)
Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 =
20)
And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&,
SND_ASYNC
Or
SND_FILENAME)

' The long statement above tries to call the alarm sound
function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an
additonal
line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command
into
the
"Private
Sub Nextzaman()" module rather then "Private Sub
Workbook_Open()"
?

Thanks for not giving up on me. I'll be waiting impatiantly
for
your
solution suggestions :)
TIA

"Bob Phillips" wrote in
message
...
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)

Zoom,

It works for me (doesn't it always!).

Cut to the quick, why don't you send me your workbook that doesn't work and
I will see if I can track it down.

My email address includes a spurious 'nothere' text, so be careful.

--

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,
Unfortunately replacing the code has no effect at all. As you may have
noticed from my previous reply to the thread, I have replaced the location
&
name of the *.wav file in Rob's code...Thus although not giving any error
messages, the macro plays the ubiquitous dong -only- (not the wav file I
specify) until the minute figure changes for 5 sec. intervals. I think we
are missing something here. Hope someone can dig it out.
Sincerely
Zoom

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

Rob's code has a small but crucial error. It accepts a filename
argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If
this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well for
"start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1 for
controlling the criteria displayed in cells D3 and D4. Now there is a
"dong"
sound (default windows alert sound) when the criteria is met. All
seems
to
be working OK, apart from not playing the desired *.wav file. My wav
file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1,
that
is
all that I have changed from your suggestion codes. I have placed the
Code
into the Module1 and Workbook code pages as well with no difference. I
am
not getting any error messages while the macro is running or when the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in
message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias
"PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed this
line
from
the original code because I thought that I could do the same
control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for two
cells
(A1
& B1). Couse the original statement format looks like it only
accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original location
of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1 = 8
And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or (Sheet1!A1
=
9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or
(Sheet1!A1
=
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20) And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&, SND_ASYNC
Or
SND_FILENAME)

' The long statement above tries to call the alarm sound function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an additonal
line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into the
"Private
Sub Nextzaman()" module rather then "Private Sub Workbook_Open()"
?

Thanks for not giving up on me. I'll be waiting impatiantly for
your
solution suggestions :)
TIA

"Bob Phillips" wrote in
message
...
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)

Zoom,

Response posted direct. Mail me if you don't get it.

Bob

"___Zoom" wrote in message
...
OK Bob, I 'll post it right away.
Thanks
Zoom

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

It works for me (doesn't it always!).

Cut to the quick, why don't you send me your workbook that doesn't work
and
I will see if I can track it down.

My email address includes a spurious 'nothere' text, so be careful.

--

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,
Unfortunately replacing the code has no effect at all. As you may have
noticed from my previous reply to the thread, I have replaced the
location
&
name of the *.wav file in Rob's code...Thus although not giving any
error
messages, the macro plays the ubiquitous dong -only- (not the wav file
I
specify) until the minute figure changes for 5 sec. intervals. I think
we
are missing something here. Hope someone can dig it out.
Sincerely
Zoom

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

Rob's code has a small but crucial error. It accepts a filename
argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If
this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

"___Zoom" wrote in message
...
Rob,
Thanks for your suggestions. Since I have P.M sessions as well for
"start
lesson" alerts (lessons 8, 9, and 10), I am using cells J1 and K1
for
controlling the criteria displayed in cells D3 and D4. Now there
is
a
"dong"
sound (default windows alert sound) when the criteria is met. All
seems
to
be working OK, apart from not playing the desired *.wav file. My
wav
file
resides in "D:\Sitem_Excel\sound.wav" and together with D1 and E1,
that
is
all that I have changed from your suggestion codes. I have placed
the
Code
into the Module1 and Workbook code pages as well with no
difference.
I
am
not getting any error messages while the macro is running or when
the
criteria is met and "dong" sounds. Hope you may find the reason...
Sincerely

"Rob van Gelder" wrote in
message
...
Zoom,

I've taken a quick look into your problem.

Here's what I can suggest:
(You may want to change E1 to match)

D1 =
=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","--")))))))

E1 =
=IF(D1<"--",PlayWavFile("C:\T\Chimes.wav"),"")

Code Module:

Private Declare Function PlaySound Lib "winmm.dll" Alias
"PlaySoundA"
(ByVal
lpszName As String, ByVal hModule As Long, ByVal dwFlags As
Long)
As
Long

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound "C:\T\chimes.wav", 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function

Public Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub

Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:00:05"), "zaman"
End Sub


Rob



"___Zoom" wrote in message
...
Here are my answers for your questions;

a) Here is the code to check the system time:
------------------------
Sub zaman()
Sheets("Sheet1").Range("A1").Value = Hour(Now())
Sheets("Sheet1").Range("B1").Value = Minute(Now())
Sheets("Sheet1").Range("C1").Value = Second(Now())
Nextzaman
End Sub
-------------------------
and here is the code that re-runs the macro at minute
intervals:
---------------------------------
Private Sub Nextzaman()
Application.OnTime Now + TimeValue("00:01:00"), "zaman"
End Sub
---------------------------------

b) For Alarm code invoking I tried to do it like this:

'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 - I removed
this
line
from
the original code because I thought that I could do the same
control
with
the long IF...statement below. I also was not sure if the
"IfEvaluate(Cell.Value & Condition) Then" format will work for
two
cells
(A1
& B1). Couse the original statement format looks like it only
accepts
"a
numerical cell value" not more...

WAVFile = "D:\Myfolder" & "\sound.wav" ' Original
location
of
my
alarm sound file

If (Sheet1!A1 = 7 And Sheet1!B1 = 55) Or (Sheet1!A1
=
8
And
Sheet1!B1 = 35) Or (Sheet1A1 = 9 And Sheet1!B1 = 15) Or
(Sheet1!A1
=
9
And
Sheet1!B1 = 55) Or (Sheet1!A1 = 10 And Sheet1!B1 = 10) Or
(Sheet1!A1
=
10
And Sheet1!B1 = 50) Or (Sheet1!A1 = 11 And Sheet1!B1 = 30) Or
(Sheet1!A1
=
11 And Sheet1!B1 = 40) Or (Sheet1!A1 = 12 And Sheet1!B1 = 20)
And
(Sheet1!A1
= 13 And Sheet1!B1 = 0) Then Call PlaySound(WAVFile, 0&,
SND_ASYNC
Or
SND_FILENAME)

' The long statement above tries to call the alarm sound
function
depending
upon the conditions
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
---------------------------------------------
Private Sub Workbook_Open()
Application.Run "zaman"
Call Alarm ' I am not sure if there needs to be such an
additonal
line
like
this here. Without this line the "zaman" module works OK.
End Sub
---------------------------------------------

Hope you have a clearer view now for my problem...

Do you think I should have added the "Call Alarm" command into
the
"Private
Sub Nextzaman()" module rather then "Private Sub
Workbook_Open()"
?

Thanks for not giving up on me. I'll be waiting impatiantly
for
your
solution suggestions :)
TIA

"Bob Phillips" wrote in
message
...
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)

Rob,

Been there.

As it happened Eser had spotted that himself and fixed it. The problem
related to the declaration of the API, he omitted a space in the first
argument, writing (ByVallpszName As String instead of (ByVal lpszName As
String, and the only error he got was the 'dong' sound, which is confusing
when you are trying to play sound(G.
--

HTH

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

"Rob van Gelder" wrote in message
...
Oops - don't you hate it when that happens.

Thanks Bob


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

Rob's code has a small but crucial error. It accepts a filename
argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If
this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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

Oops - don't you hate it when that happens.

Thanks Bob


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

Rob's code has a small but crucial error. It accepts a filename argument,
but ignores it and tries to play a file called "C:\T\chimes.wav". If this
doesn't exist it will play the ubiquitous dong.

Replace it with this

Public Function PlayWavFile(WavFile As String) As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFile = ""
End Function


--

HTH

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