if function to activate a macro

[jonwhite]

Hi all,

Can an IF function activate a macro??
I have recored a macro and want to be able to put it into a IF function for ex:
=IF(a1>1,Macro,"")

Many Thanks

Jon.

 

[Smitty]

Not really, but you can use a Sheet_Change event:

<font face=tahoma><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">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
       <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target > 1 <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Cell " & Target.Address & " will now trigger your Macro"
            <SPAN style="color:#007F00">'   Call your Macro here</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>

Hope that helps,

Smitty