Hi,

I want to trigger a script whenever the data changes in a cell.
For testing purposes I used this little script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c1")) Is Nothing Then
Range("a1") = 1
End If
End Sub

The problem:
If I type something in c1 everything goes ok
But when I put this in c1= e1 and change the data in e1, c1 changes but
nothing happens to a1

How can I trigger the script thru a link that changes?

Thanks


--
climax
------------------------------------------------------------------------
climax's Profile: http://www.excelforum.com/member.php...o&userid=30816
View this thread: http://www.excelforum.com/showthread...hreadid=507580

Hi Cilimax,

If the value of C1 depends on E1, try changing:

If Not Intersect(Target, Range("c1")) Is Nothing Then

to

If Not Intersect(Target, Range("E1")) Is Nothing Then


---
Regards,
Norman



"climax" wrote in
message ...

Hi,

I want to trigger a script whenever the data changes in a cell.
For testing purposes I used this little script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c1")) Is Nothing Then
Range("a1") = 1
End If
End Sub

The problem:
If I type something in c1 everything goes ok
But when I put this in c1= e1 and change the data in e1, c1 changes but
nothing happens to a1

How can I trigger the script thru a link that changes?

Thanks


--
climax
------------------------------------------------------------------------
climax's Profile:
http://www.excelforum.com/member.php...o&userid=30816
View this thread: http://www.excelforum.com/showthread...hreadid=507580

You can use this code snippet:
***
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C3").Formula = "=" & Mid(Target.AddressLocal, 2, 1) &
Target.Row & "" Then
Range("A1") = 3
End If

End Sub
***

If the cell (address) changes that has been referenced (in this case in
'C3') then cell 'A1' has value of 3.
I tested briefly and it seems to work.

Thanks JK
I´m one step closer now but the problem is I get data for forex trading
into my exel sheet. The formula for cell c3 is: =IQLink|heurusd!ASK
I get the askprice from euro vs dollar from an extern prog in cell c3.
The data changes constant +- once a second. I think that the problem is
there is a formula in cell c3 and exel won´t recognise the data change.
It only sees the formula and no value. Is there a way around this ?

thanks


--
climax
------------------------------------------------------------------------
climax's Profile: http://www.excelforum.com/member.php...o&userid=30816
View this thread: http://www.excelforum.com/showthread...hreadid=507580

ok I see. Well I don't access to Reuters/Bloomberg/similar on my
present job
but here's what I drafted.
Instead of selection/worksheet changes I used 'Calculate' as trigger.
It works when I press F9 to calculate manually.
I hope it works with outside feed too.

***
Option Explicit
Dim vPreValue As Variant

Private Sub Worksheet_Calculate()

If Range("C3").Value < vPreValue Then
Range("A1") = "Changed!"
Else
Range("A1") = ""
End If

vPreValue = Range("C3").Value
End Sub
***

regs,
JK