Formula trigger a macro??

[Jeff Wright]

Is it possible for a formula to trigger a macro?? I want to do something
like:

=IF(X=10,[run the macro],"")

Thanks for your help!

Jeff

 

[Frank Kabel]

Hi Jeff
AFAIK this is not possible. One workaround would be to use the
woksheet_change event and insert your code there. e.g.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("X1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
With Target
If .Value = 10 Then
' Application.EnableEvents = False
'insert your code here or call another macro
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank

 

[Earl Kiosterud]

Jeff,

A user-defined function might work. It's a macro called by the cell, except
it can't change stuff on the worksheet as can a Sub (which you're probably
thinking of when you say macro). Written in VBA, just like a sub. You can
pass stuff to it, and get a value back.
=IF(X=10, MyFunction(),"").
=IF(X=10, MyFunction(10, B2, etc),""). Can't say more without knowing what
you want to do.

 

[Jeff Wright]

Thanks, Earl. I'll try it out and see what happens.

Jeff

Jeff,

A user-defined function might work. It's a macro called by the cell, except
it can't change stuff on the worksheet as can a Sub (which you're probably
thinking of when you say macro). Written in VBA, just like a sub. You can
pass stuff to it, and get a value back.
=IF(X=10, MyFunction(),"").
=IF(X=10, MyFunction(10, B2, etc),""). Can't say more without knowing what
you want to do.


--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Is it possible for a formula to trigger a macro?? I want to do something
like:

=IF(X=10,[run the macro],"")

Thanks for your help!

Jeff

 

[Jeff Wright]

Thanks, Frank. I'll experiment with your idea and see what happens!

Jeff

Hi Jeff
AFAIK this is not possible. One workaround would be to use the
woksheet_change event and insert your code there. e.g.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("X1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
With Target
If .Value = 10 Then
' Application.EnableEvents = False
'insert your code here or call another macro
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank

Is it possible for a formula to trigger a macro?? I want to do
something like:

=IF(X=10,[run the macro],"")

Thanks for your help!

Jeff

 

[Earl Kiosterud]

Jeff,

It appears you'll be experimenting with a Worksheet_Change sub (Frank's
suggestion), and a User-Defined Function (mine). Here's some things to
consider:

The UDF will get run only when the worksheet calculates, and only when any
of the arguments to your function change.* It can't change any values in
the worksheet. The change sub will run any time any change is made to the
worksheet. It examines the cell changed (target) to see if it's the one of
interest, then runs your code. It can change stuff on the worksheet.

*You can tease the function into running at any worksheet calculate by
having it in a cell formula that refers to itself. You can avoid the
circular reference by turning on Iterations in Tools - Options - Calculate.
Set Max Iterations to 1. If the function is x(), the formula might be in A1
and look like this:

= A1 + x()

It can only run about a quadrillion times before the value in the cell gets
too big for Excel's internal number representation. Good chance you'll have
to reboot before that happens anyway!
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Thanks, Earl. I'll try it out and see what happens.

Jeff

Jeff,

A user-defined function might work. It's a macro called by the cell, except
it can't change stuff on the worksheet as can a Sub (which you're probably
thinking of when you say macro). Written in VBA, just like a sub. You can
pass stuff to it, and get a value back.
=IF(X=10, MyFunction(),"").
=IF(X=10, MyFunction(10, B2, etc),""). Can't say more without knowing what
you want to do.


--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Is it possible for a formula to trigger a macro?? I want to do something
like:

=IF(X=10,[run the macro],"")

Thanks for your help!

Jeff