Bookmark and Share

Enter your email address:

Delivered by FeedBurner

Home Page

Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker

  Saturday, July 19, 2008 – Permalink –

Run a Macro from 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:


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.

Worksheet code


Also see:
Change Events

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

After posting this, Ross Mclean of 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

You can see the simple coding here:

Running a macro from a cell.

Thanks Ross!

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
End Sub

when invoked by this worksheet formula:


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

[Edited entry from 3/26/2005]

See all Topics


<Doug Klippert@ 2:05 AM

Comments: Post a Comment

Links to this post:

Create a Link