MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Call a macro from a Cell


Posted by Hugo on January 02, 2002 12:19 PM

How can I run a macro from a Cell with an IF statement.


Posted by Tom Urtis on January 02, 2002 12:49 PM

You need to involve a worksheet change event that monitors changes in values to a target cell, in order to trigger your macro. Here are two examples of ways to go about it:


Example, run macro if "XYZ" is entered in A1, use Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, [A1]) Is Nothing And Target = "XYZ" Then
YOUR MACRO
End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then YOUR MACRO
End Sub


Remember, these code examples (only one, you pick) would reside in the worksheet module, so right-click on the sheet tab, left click on View Code, and paste one of them in. And typically you'll have the actual macro stored in a standard VBA module.

HTH

Tom Urtis

Posted by Hugo on January 03, 2002 11:33 AM

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)