is it possible to run a Macro from a Cell with formula, Ex. =IF($B21="","",MACRO)

Posted by hugo on January 06, 2002 2:09 PM

Tom, thank you. I guess I did not explain it well. I would like to place a formula on a cell so that it will call a macro if the value is place in another cell.
Ex. =IF($B21="","",MACRO)

Posted by Dank on January 06, 2002 2:49 PM

Hi Tom,

You can't execute a macro from within a function. The only thing a function can do is return a value. However, you can monitor the state of a cell using a worksheet event. To use your example, right click the worksheet tab and choose View Code. Try something like this:-

Private Sub Worksheet_Calculate()
If Me.Range("B21") = "" Then YourMacro
End Sub

Sub YourMacro()
MsgBox "B21 is empty!"
End Sub

YourMacro could either be in the worksheet code module or in a standard module.

HTH,
Daniel.