Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









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


  Thursday, May 28, 2015 – Permalink –

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.


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

Labels: , , , , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment