Launching macro based on value of a cell

[CHawk]

Is there anyway to automatically launch a Macro every time a particular cell changes from False to True? Any help with this would be greatly appreciated.

 

[CHawk]

Well I am thinking that it will because I tested some of the code that Justin gave me earlier in this thread on Friday when the DDE link up in realtime. His code triggered my macro everytime any cell in the worksheet was updated via DDE. So I have my fingers crossed. I will report back on Monday and let you know.


Cathy

 

[just_jon]

Well I am thinking that it will because I tested some of the code that Justin gave me earlier in this thread on Friday when the DDE link up in realtime. His code triggered my macro everytime any cell in the worksheet was updated via DDE. So I have my fingers crossed. I will report back on Monday and let you know.


Cathy

See! I *told* you I was Stoo-pid! Hope it does work.

Note that in my code an update which changes the underlying cells but results in cell I11 "changing" from True to True will not count as a qualifying event - does it need to? [ I had assumed you did not want that counted, but now am unsure. ]

 

[CHawk]

You assumed correctly, I didn't want that to count. Well I hope it works and only Monday morning will tell. But hey if it doesn't, could using "worksheet_calculate event" work? I really don't know what that event is but it sounds like something that might work. Cathy

 

[just_jon]

That would be the next step, I suppose, as long as Calc is set to automatic - which, as you're doing constant updates, should be.

 

[CHawk]

Jon: You were right -- it didn't launch the macro. If you could help me with the worksheet_calculate event, it would be much appreciated.

Cathy

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
<SPAN style="color:#00007F">Static</SPAN> MyState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.Calculation = xlCalculationManual
<SPAN style="color:#00007F">If</SPAN> [I11].Value = MyState <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">' Here if I11's value has not changed.</SPAN>
    <SPAN style="color:#00007F">GoTo</SPAN> Xit
<SPAN style="color:#00007F">ElseIf</SPAN> [I11].Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MyState = [I11].Value
    <SPAN style="color:#007F00">' YOUR CODE HERE</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Xit:
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

See if this code works - goes in the same sheet module, delete the other event code.

 

[CHawk]

I am getting the following error when I paste it.

Compile error:

Ambiguous name detected: Worksheet_Calculate



Cathy

 

[just_jon]

You already have a Worksheet_Calculate event macro on that sheet.

We need to either A] delete that one or, B] incorporaste this one into it.

Can have at most 1 of any event-type macro on a sheet.

 

[CHawk]

Jon: Here is the other calculate code that is in that worksheet. Where do I put your code?

Private Sub Worksheet_Calculate()
Dim var As Variant
var = Range("j8").Value

If VarType(var) = vbDouble Then
Dim val As Double
val = var

If val >= 95.73 Then
' uncomment the next line to place the order
'Call Order3
End If
End If
End Sub

 

[just_jon]

The code you have doesn't actually do anything - is the Call line ever un-commented?