Run a Macro From a Cell

How to do the impossible (almost)

There are times when it might be nice to run a macro from a cell function.
Something like : if a cell has a certain value, a macro will run:

=IF(A1>10,Macro1)

You can not initiate a macro from a worksheet cell function. However, you can use the worksheet's Change event to do something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value > 10 Then
MsgBox "Put your macro code here in place of the MsgBox line"
End If
End If
End Sub

When A1 is changed to a value greater than 10, the macro code will run.

To get to the Worksheet Event code, right-click the sheet tab and choose View Code.




From CPearson.com
Also see:
Change Events

Also:
Microsoft KnowledgeBase:
How to Run a Macro When Certain Cells Change

After posting this, Ross Mclean came up with a great work around using a User Defined Function.

Public Function RMAC(ByVal Macro_Name As String,  _

ByVal Arg1 As Variant)

RMAC = Application.Run(Macro_Name, Arg1)

End Function

Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.

For example (watch line wrap), this VBA code:

Public Function RMAC _
(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run _
(Macro_Name, Arg1)
End Function

Sub MyMacro(arg As String)
ActiveCell.Interior.ColorIndex _
= 3
Beep
End Sub

when invoked by this worksheet formula:

=rmac("MyMacro","yada")

runs the sub MyMacro with some modification. The Beep is executed, the cell color change is not.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/



See all Topics