email on condition from "import external data"

I am trying to get an excel worksheet to email me when a cell value changes. I have been able to accomplish this if I manually change the cell data or if it changes by formula.

My problem is, when the cell data changes by receiving new data from a refresh of "import external data", the data change will not invoke my send mail macro.

Any ideas?

Check out the calculation event (in the worksheet module, not a regular
module)
You might be able to set up a dummy cell with a formula and check it each
time the worksheet calculates. Or monitor a cell that has a formula
dependent on the cell you want to watch.

--
steveB

Remove "AYN" from email to respond
"CAP" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to get an excel worksheet to email me when a cell value
> changes. I have been able to accomplish this if I manually change the
> cell data or if it changes by formula.
>
> My problem is, when the cell data changes by receiving new data from a
> refresh of "import external data", the data change will not invoke my
> send mail macro.
>
> Any ideas?
>
>
> --
> CAP
> ------------------------------------------------------------------------
> CAP's Profile:
> http://www.excelforum.com/member.php...o&userid=27720
> View this thread: http://www.excelforum.com/showthread...hreadid=472351
>

Steve, Thanks for replying.

Actually I just used the example from http://www.rondebruin.nl/mail/change.htm

The worksheet module looks as follows

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("c1"), rng) Is Nothing Then
If Range("c1").Value > 0 Then Sendmail
End If
End If
EndMacro:
End Sub

I do have a test workbook (workbook 1) with a worksheet setup with the above. C1 changes depending on data imported into A2 and A3 from an another workbook (workbook 2). C1 is a formula (=IF(AND(A2>=40,A3<=40),1,-1))

If I change the data in (workbook 2) to make C1=1 in (workbook 1), I get no email.

If I manually change A2 and A3 in my worksheet in (workbook 1) to make C1 =1 I get email.

I agree that it must be in worksheet module script, I just don't have a clue what is wrong with the script, or how it even knows or cares that A2 and A3 have been changed manually or by imported data.

Cap,

If it comes from Ron or any of the other guru's, than ignor me. They really
know this stuff.

I haven't done much with capturing changes like you seem to want. Most of
what I do is capture changes to a specific cell using the worksheet change
event:
If Target.Address = "$A$1" then

where target is the cell that is changed. This might be incorporated into
an importing code or triggered by the code.

Keep in touch...

--
steveB

Remove "AYN" from email to respond
"CAP" <[email protected]> wrote in message
news:[email protected]...
>
> Steve, Thanks for replying.
>
> Actually I just used the example from
> http://www.rondebruin.nl/mail/change.htm
>
> The worksheet module looks as follows
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
> If Target.Cells.Count > 1 Then Exit Sub
> On Error GoTo EndMacro
> If Not Target.HasFormula Then
> Set rng = Target.Dependents
> If Not Intersect(Range("c1"), rng) Is Nothing Then
> If Range("c1").Value > 0 Then Sendmail
> End If
> End If
> EndMacro:
> End Sub
>
> I do have a test workbook (workbook 1) with a worksheet setup with the
> above. C1 changes depending on data imported into A2 and A3 from an
> another workbook (workbook 2). C1 is a formula
> (=IF(AND(A2>=40,A3<=40),1,-1))
>
> If I change the data in (workbook 2) to make C1=1 in (workbook 1), I
> get no email.
>
> If I manually change A2 and A3 in my worksheet in (workbook 1) to make
> C1 =1 I get email.
>
> I agree that it must be in worksheet module script, I just don't have a
> clue what is wrong with the script, or how it even knows or cares that
> A2 and A3 have been changed manually or by imported data.
>
>
> --
> CAP
> ------------------------------------------------------------------------
> CAP's Profile:
> http://www.excelforum.com/member.php...o&userid=27720
> View this thread: http://www.excelforum.com/showthread...hreadid=472351
>

Thanks Steve for being the only one to reply. I have found the flaw in the worksheet module.

Cap,

You're welcome! Glad you were able to figure it out.

My only guess is that it appears you were already given the solution and
nobody wanted to continue the discussion.

In the future it may help if you note your level of expertise and ask
questions - being as clear as possible. Describe what you don't understand
or are having trouble with. Give as much detail as possible.

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"CAP" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Steve for being the only one to reply. I have found the flaw in
> the worksheet module.
>
>
> --
> CAP
> ------------------------------------------------------------------------
> CAP's Profile:
> http://www.excelforum.com/member.php...o&userid=27720
> View this thread: http://www.excelforum.com/showthread...hreadid=472351
>