Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 "J_J" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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. -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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;B10;B1<40);"8.ders";IF(AND(A1=14;B1 40);"9.ders";IF(AND(A1=15;B1<20);"9.ders";IF(AND( A1=15;B130);"10.ders";IF(AND(A1=16;B1<10);"10.der s";"_"))))) Thank you for your time Regards J_J "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 "J_J" wrote in message ... 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;B10;B1<40);"8.ders";IF(AND(A1=14;B1 40);"9.ders";IF(AND(A1=15 ;B1<20);"9.ders";IF(AND(A1=15;B130);"10.ders";IF( AND(A1=16;B1<10);"10.ders" ;"_"))))) Thank you for your time Regards J_J "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "J_J" wrote in message ... 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;B10;B1<40);"8.ders";IF(AND(A1=14;B1 40);"9.ders";IF(AND(A1=15 ;B1<20);"9.ders";IF(AND(A1=15;B130);"10.ders";IF( AND(A1=16;B1<10);"10.ders" ;"_"))))) Thank you for your time Regards J_J "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
In your general module add the declaration at the top outside any procedu
'------------------ 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 '---------------------------- -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does it sounds every minute?-Help please
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 "Tom Ogilvy" wrote in message ... In your general module add the declaration at the top outside any procedu '------------------ 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 '---------------------------- -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "J_J" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sounds | Excel Discussion (Misc queries) | |||
sounds in Excel | Excel Discussion (Misc queries) | |||
Excel Sounds | Excel Discussion (Misc queries) | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
convert 100 minute hour to a 60 minute hour | Excel Worksheet Functions |