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
  Web http://www.klippert.com



  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:

=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.


Worksheet 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 of Methodsinexcel.co.uk 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
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/


[Edited entry from 3/26/2005]




See all Topics

Labels:


<Doug Klippert@ 2:05 AM

Comments: Post a Comment

Links to this post:

Create a Link