why does it sounds every minute?-Help please

[J_J]

The below code

'-------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
For Each c In Range("D2:G5")
If Not Application.Intersect(Target, c.Precedents) Is Nothing Then
Interaction.Beep
End If
Next c
On Error GoTo 0
End Sub
'----------------------


sound on each minute change of my system clock. You see, cells
A1 and B1 displays the system clock hour and minute values. And the watching
cells (Range D2:G5) look to the values displayed in A1 and
B1. Then the main macro is executed in minute intervals to let A1 and
B1 refresh their displays. Now although no "display" change is valid in the
watched cells the sound alarm beeps in minute intervals too. Maybe my other
suggestion may prevent that. I hope I didn't confuse you...
Hope we can solve this problem.
Regards
J_J

 

[Bob Phillips]

Test for the watched cells

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
If Not Intersect(Traget,Range("D2:G5")) Is Nothing Then
For Each c In Range("D2:G5")
If Not Application.Intersect(Target, c.Precedents) Is Nothing
Then
Interaction.Beep
End If
Next c
End If
On Error GoTo 0
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[J_J]

Sorry for my mistake...
The last sentences should have been:

Now although no "display" change is valid in the watched cells the sound
alarm beeps in minute intervals too.
Hope we can solve this problem.


p.s.: and apologies for posting to two different NG's the same Q.
It was not intended. I post by mistake.

Regards
J_J

 

[Tom Ogilvy]

the change event fires when a cell is manually edited, changed by code, or
updated through a DDE formula or similar.

Whatever causes the A1 and B1 to updated must be making a change (that
triggers the change event) to a precedent cell of the watched range. For
example, if the watched cells have formulas that refer to cells A1 and/or B1
and you change them (A1 or B1), then that would trigger your beep.

I don't know what is causing A1 and B1 to change, so I can't say what you
need to do.

 

[Tom Ogilvy]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng as Range
On Error Resume Next
set rng = Range("A1:B1")
' don't trigger on a change to A1 or B1
if not intersect(rng,Target) is nothing then exit sub
For Each c In Range("D2:G5")
If Not Application.Intersect(Target, c.Precedents) Is Nothing Then
Interaction.Beep
End If
Next c
On Error GoTo 0
End Sub

 

[J_J]

Hi Bob,

No...sadly the problem is still here with me.
It is not sounding as the cell displays change but it sounds in minute
intervals with the 'Nextzaman' module.
Here are the other codes from the workbook if it may help...

In Module1 I have this:
'------------------
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:01:00"), "zaman"
End Sub
'--------------------------

In Sheet1 code page I have your code

and in ThisWorkbook code page I have:

'--------------------
Private Sub Workbook_Open()
Application.Run "zaman"
End Sub
'----------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
'----------------------------

In cells in range D2:G5 I have expressions like this:

=IF(AND(A1=14;B1>0;B1<40);"8.ders";IF(AND(A1=14;B1>40);"9.ders";IF(AND(A1=15;B1<20);"9.ders";IF(AND(A1=15;B1>30);"10.ders";IF(AND(A1=16;B1<10);"10.ders";"_")))))

Thank you for your time
Regards
J_J

 

[Bernie Deitrick]

J_J,

Try turning off events when you update your cell values:

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

HTH,
Bernie
MS Excel MVP

Hi Bob,

No...sadly the problem is still here with me.
It is not sounding as the cell displays change but it sounds in minute
intervals with the 'Nextzaman' module.
Here are the other codes from the workbook if it may help...

In Module1 I have this:
'------------------
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:01:00"), "zaman"
End Sub
'--------------------------

In Sheet1 code page I have your code

and in ThisWorkbook code page I have:

'--------------------
Private Sub Workbook_Open()
Application.Run "zaman"
End Sub
'----------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
'----------------------------

In cells in range D2:G5 I have expressions like this:

=IF(AND(A1=14;B1>0;B1<40);"8.ders";IF(AND(A1=14;B1>40);"9.ders";IF(AND(A1=15

 

[J_J]

Hi Tom,

Yours and Bernie Deitrick's proposals achieved a solution of stopping
"false alarms" (beeps that occur because of the system clock minute changes
reflected by another macro). But now although there is no sound beeps on
minute changes, there is no sound either on changes with the watched range
display. Possibly I am missing something...or we all are.

Regards
J_J

 

[J_J]

Hi Bernie,

Yours and Tom's proposals achieved a solution of stopping
"false alarms" (beeps that occur because of the system clock minute changes
reflected by another macro). But now although there is no sound beeps on
minute changes, there is no sound either on changes with the watched range
display. Possibly I am missing something...or we all are.

Regards
J_J

 

[Tom Ogilvy]

In your general module add the declaration at the top outside any procedure:
'------------------
Public vArr as Variant

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:01:00"), "zaman"
End Sub
'--------------------------

'In Sheet1 code page I have your code (alter as shown)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng as Range
Dim i as Long, j as Long
Dim bChange as Boolean
On Error Resume Next
if Target.Address = "$C$1" Then
bChange = False
For Each c In Range("D2:G5")
i = c.row - 1
j = c.column - 3
if c.Value <> vArr(i,j) then
bChange = True
exit for
end if
Next c
if bChange then
Interaction.Beep
vArr = me.Range("D2:G5").Value
End if
End if
On Error GoTo 0
End Sub

'and in ThisWorkbook code page I have:

'--------------------
Private Sub Workbook_Open()
Application.Run "zaman"
vArr = Sheets("Sheet1").Range("D2:G5"
End Sub
'----------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
'----------------------------

 

[J_J]

Hi Tom,
That did it !
Reading the whole watched range into a variable and then checking for any
change to activate the alarm sound was magnificent.
You are not judt teaching me "how" to code but how to "think for
alternatives" as well. Your Worksheet_Change sub is superb.
Thanks a million...
J_J