Running a macro from a cell formula

[Hall]

I want my cell to have the formula

=if(A1=1,<run-macro>,)

If a macro <run-macro> exists, what's the syntax to have the formula run it?

 

[Don Guillett]

I answered this somewhere today

Use a worksheet change event by right click on sheet tab>view code>insert
this>modify>save
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub
Call mysub
End Sub
Sub mysub()
MsgBox "Hi"
End Sub

 

[Hall]

Um...Don I know there's an example in there for me... but I'm not that
familiar with this yet to be clear on such a cryptic explanation. I'd
appreciate if you could add a bit more to this...

Thx

 

[Brian Tozer]

I too am really hoping for a more complete description of exactly how to
implement this operation.
Brian Tozer

 

[alcorjr]

I hate to barge in on thread, but couldn't help myself:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub
Call mysub
End Sub







The line:
If Target.Address <> "$F$1" Then Exit Sub

assures you, that the Call mysub would only work when
the change sheet event happens on cell F1.

For changes in any other cells, it sends you to;
Exit Sub

that is; it'll do nothing

you have to go to the vba editor, on the left side of the screen click
on the sheet you want the codse to work on, and then
click on the right hand-side drop-down box, and select "change".

Copy-paste the procedure there.

Hope it helps

 

[alcorjr]

On second thought, and if what you really want to do is test if A1 =1 t
start a procedure, maybe you should try this:


Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("A1")=1 Then

Call Your Macro

Else
Exit sub
End IF
End Su

 

[Brian Tozer]

Many thanks for helping.
My query is inserted below.

On second thought, and if what you really want to do is test if A1 =1
to start a procedure, maybe you should try this:


Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("A1")=1 Then

Call Your Macro

At this point do I literally enter the above phrase, ("Call Your Macro") or
if not, exactly what in this example do I enter?

 

[alcorjr]

After Call
Just write the name of your macro


There are two ways to do this, p.ex.

Let's say that you want a macro that will add the value of B1 t
whatever is in A1, only when A1 =1. You do this:

Sub add()
Activesheet.Range("A1") = Activesheet.Range("A1")
Activesheet.Range("B1")
End Sub

If you write Call add in the example of my previou
post, the macro "ADD" will run only when A1 = 1


Or you can save yourself the "Call" command, and just write th
previous script after the condition.

Replace the Call Add command
with:

Activesheet.Range("A1") = Activesheet.Range("A1")
Activesheet.Range("B1")

Hope it helps.

Merry Xma

 

[Don Guillett]

So solly. In my haste to get things done and so shopping, I forgot a lineif target=1 then Call mysub
===
could be written as one liner

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$F$1 and target =1 then call mysub
end sub