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]

No, I don't use it. I deleted the code and replaced it with yours, the macro launched once and then when it changed from false to true again nothing happened.

I closed excel and then reopened it to see if it would fire again and I got an error -- when I hit debug it highlighted this line of code

If [I11] Value = MyState Then


Sorry this is taking so much of your time, 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.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>
    <SPAN style="color:#007F00">' YOUR CODE HERE</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Xit:
MyState = [I11].Value
Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

See if this works - remember you have to add your actions/code at the line YOUR CODE HERE

 

[CHawk]

No, It doesn't work. I get the same error and it highlights the same line of code as before.

 

[just_jon]

What error message?

 

[CHawk]

Run-time Error '13'

Type Mismatch

then I hit debug and it highlights:

If [I11].Value = MyState Then

 

[just_jon]

Is MyState declared as Variant in your event code as above? Is it declared anywhere else?

 

[CHawk]

IT WORKS! IT WORKS! The Mystate as Variant was okay but I noticed a line above your code that must have been part of the previous code that I deleted that said "Dim orderPlaced As Boolean". I deleted that line and your code is firing perfectly. THANK YOU SO VERY MUCH FOR ALL YOUR HELP.

Cathy

 

[toojeep4u]

Cathy, you said you are acting on live data from a DDE link, would that be coming from RSLinx perhaps? Because this is exactly what I am trying to do.