Worksheet Change event and Links?

  • Thread starter Thread starter emge
  • Start date Start date
E

emge

Hi, from what i've read what i have should work. Can anyone tell me
what i'm doing wrong?

I have a workbook that has one piece of data in it. In cell A2 i have
a Link to a server [=OPCLINK|Test!'rpr:data address'] to pull a binary
bit which toggles roughly every half hour or so. I want to use this as
a trigger. When this bit goes to a one I want it to open another
workbook, run some macros to update the data there then save with the
current date and time. If I go to Cell A2 and type '1' and hit enter
everything works just fine. But if the link updates and A2 goes to a
one, I get nothing. I've tried a few different things like adding a
calculation to the sheet adding 0 to A2 to see if I could get it to
fire off of a calculation chage. But still , nothing.

Here is the code:


Code:
--------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myval As Integer
myval = Sheets("Trigger").Range("A2").Value2
If myval = 1 Then
Workbooks.Open blah blah
Application.OnTime blah blah
Application.OnTime blah blah
Application.OnTime blah blah
Else
End If
End Sub
--------------------


If anyone could help me out here it would be greatly appreciated. I've
been working on this a while now and its starting to drive me mad.
Heh.

Thanks for any replies,
Mike
 
Change wouldn't fire on this type of link. Have a formula reference that
cell and use the calculate event.
 
Ok. Heres what I did and what happened.

In C2 I added the following formula, [=SUM(A2,0)]. And I change the
code to this:


Code:
--------------------

Private Sub Workbook_SheetCaclulate(ByVal Sh As Object)
Dim myval As Integer
myval = Sheets("Trigger").Range("C2").Value2
If myval = 1 Then
Workbooks.Open blah blah
Application.OnTime blah blah
Application.OnTime blah blah
Application.OnTime blah blah
Else
End If
End Sub

--------------------


And I get a 'Type Mismatch' error on Line Three.

Thanks so much for the quick response.
Mike
 
i was trying to do something like that but i kept getting a circular
reference error. i pretty much did the same thing just using an extra
cell and seperating the two functions out there. (the isna and the sum
functions). Seems to be working great now. Finally.

Thanks so much for your help. I really appreciate it.

Mike

scratch all that, i figured out why i was getting the circular error.
my bad. hehe.
 
Back
Top