can VBA be used to sound a *.wav file on condition(s)?

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
>> 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

Dear Jake,
The watched cells are
D2, D3, E2, E3, F2, F3, F4, F5, F6, G2, G3, and G4.
These cells change their display according to the values read from A1 and B1
which displays the hour and time of system clock.
Normally thouse watched cells all display a "_" character if the conditions
in their formulas does not force them to change their display.
Can we modify your macro so that if any one of thouse cells changes its
displayed value then the sound beeps?.
J_J

"Jake Marx" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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

Hi Jake,
That is correct. And on the above thread (opened by me with same question
news:#[email protected]) 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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
>

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 news:#[email protected]) 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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

Hi Jake,
Unfortunately that did not solve the problem as well.
But Tom found a solution that cures this problem. You can have a look at it
a few threads up.
Thank you so much for your efforts and patience with me.
Regards
J_J

"Jake Marx" <[email protected]> wrote in message
news:[email protected]...
> 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 news:#[email protected]) 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> 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