Hi J_J,
Maybe something like this would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Application.Union(Target, Range("A1:B1") _
).Cells.Count 2 Then
On Error Resume Next
For Each c In Range("D2:G6")
If Not Application.Intersect(Target, _
c.Precedents) Is Nothing Then
If Err.Number = 0 Then Interaction.Beep
End If
Next c
End If
End Sub
Let me know....
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
J_J wrote:
Hi Jake,
That is correct. And on the above thread (opened by me with same
question ) which solution
suggestions were given by Bob, Bernie and Tom all 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
"Jake Marx" wrote in message
...
Hi J_J,
Let's see if I follow what you're looking to do. You would like the
beep to occur if any of the cells referenced by the "complex IF
formula" cells change. But you don't want the beep if the only
cell(s) that changed were A1 and/or B1. Is that correct?
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
J_J wrote:
Jake,
I modified your code handling the error control as you suggested and
now receive no error messages. Thanks for that. But the trouble is
that now I get the sound alert 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 (list given in my other reply)
look to the values displayed in A1 and B1. The main macro (not
yours) 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
"Jake Marx" wrote in message
...
Hi J_J,
Sorry - I should have caught that. You can put an On Error Resume
Next at the beginning of the code and an On Error Goto 0 at the end
of the code to fix it.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
J_J wrote:
Hi Jake,
I received a "no cells were found" error (Run time error '1004')
when activating the workbook for the
If Not Application.Intersect(Target, c.Precedents) Is
Nothing Then
line of your code. I've changed my region to D2:G6...as it is the
region I'd watch.
Any ideas why?
J_J
"Jake Marx" wrote in message
...
Hi J_J,
You should be able to do this with the Change event of the
Worksheet object. Copy the following code into the worksheet's
code module (right-click your sheet tab and select View Code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Range("A6:D6")
If Not Application.Intersect(Target, _
c.Precedents) Is Nothing Then
Interaction.Beep
End If
Next c
End Sub
Just change the A6:D6 to the range containing the 10-15 cells
that contain the formulas you want to watch.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address
unmonitored]
J_J wrote:
Hi,
My question is on the subject line...
I have a worksheet which has 10-15 cells with long IF statements
as formulas.
The cell formulas are in the form of (not exactly of course):
=IF(A1=condition1 OR B1=condition2, display"first message", else
if(A1=condition2 AND B1=condition3), display"second message",
else display "third message")
etc.
I need to use my laptops internal speaker to sound an alarm
(bip) or call a *.wav file to play.... "if" any one of the cell
value is "changed" because of the conditions on any one of the
cell is changed .
Can this be done via VBA?. I'd appreciate if code samples can be
given.
TIA
J_J