email on condition from "import external data"

[CAP]

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?

 

[STEVE BELL]

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.

 

[CAP]

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.

 

[STEVE BELL]

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...

 

[CAP]

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

 

[STEVE BELL]

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...