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.

 

[just_jon]

UNTESTED, but I was thinking of something like --

in ThisWorkbook:

<font face=Courier New>Static MyState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> workbook_open()

    MyState = Worksheets("Sheet1").Range("A1")
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

in the Sheet1:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target.Address, [A1]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">' Here if A1 changed</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Value = MyState <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">' Here if A1's value has not changed.</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#007F00">' Here if A] A1 changes and B] it is = True and it</SPAN>
            <SPAN style="color:#007F00">' didn't "change" from False to False</SPAN>
            MyState = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#007F00">' Your Code here</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[CHawk]

Juston: Your code does work I typed in the wrong value.

Jon: I am not quite sure how to do what you gave me. In ThisWorkbook I already have

Private Sub Workbook_Open()

End Sub

Am I suppose to add below that statement

Static MyState AS Boolean
Private Sub Workbook_Open()
MyState = Workseets("Sheet1").Range("A1")
End Sub

Or is it meant to replace what I already have.

Also Do I leave Sheet1 and A1 as is or were they just examples. My Sheet1 is Called "Tickers" and A1 is currently blank. The True/False cell that updates is cell I11 on "Tickers".

I am getting compile errors in both This Workbook and sheet1

My Macro is LogES is there a way that you could tell me exactly what to put in each. Also, Is this to replace what Justin gave me or is it in addition to?


Thanks for your help, Cathy

 

[just_jon]

The 2 macros I posted must replace any other Open/Change event macros on the ThisWorkbbook/sheet modules.

Yes, you should change Sheet1/A1 to reflect your sheet/cell names.

Out of time tonight, but I will check back tomorrow.

Everyone have a safe New Year!

 

[just_jon]

Sorry for the garbage earlier -- this seems to test well, if I understand the requirement.

IGNORE all of my prior code [ some would argue "in all threads" ] and this either as your only worksheet change event on the sheet module, or incorporated into an existing sheet change event.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<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>
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [I11]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">' Here if I11 changed</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.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> Target.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>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Xit:
MyState = Target.Value
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[CHawk]

Jon: Thanks for the reply. It is almost working. Here is the only problem. Cell I11 is actually a formula that gives me a True or False result. If I change the values in other cells that result in a False and then change the values that result in a True, nothing happens with your code. However, if I go to cell I11 and type in False and then type in True the macro kicks in prefectly. I guess the code is thinking the the formula has not changed so nothing is happening eventhough the result of the formula has. Is there anyway of fixing this?

Thanks for all your help we (I should say you) are so close to getting this to work.

Cathy

 

[just_jon]

What is the formula in I11 - looks as if we'll have to target the referenced cells for changing ...

 

[CHawk]

The formula currently just says =if(or(I26 >= 1000,L26 >= 1000),"True","False") but I often change that formula to >= 800 or some other number.

 

[just_jon]

Overwrite with this one and let me know how it tests.

EDIT: Please tell me I26/L26 are manual entries ...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">Static</SPAN> MyState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, SourceRange <SPAN style="color:#00007F">As</SPAN> Range
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> SourceRange = Application.Union([I26], [L26])
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, SourceRange) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">' Here if I26 or L26 has changed.</SPAN>
    <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>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Xit:
MyState = [I11].Value
<SPAN style="color:#00007F">Set</SPAN> SourceRange = <SPAN style="color:#00007F">Nothing</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[CHawk]

Jon: Yes that works. These two cells are updated via DDE links in realtime. Since the DDE link is not up and running over the weekend, I had to type in the values in these cells. So I won't know for sure until Monday. Should it be okay if they're DDE links? Or is that going to be treated the same as a formula.

Thanks again for all your help, Cathy

 

[just_jon]

My guess is that it will not trigger an event.

EDIT Pardon the ignorance but how is the DDE link fired/called/set in motion? Don't know beans about that stuff

But, maybe in the process that fires up that linking you can do your checking.