If "true" run a Macro
 

How can I have an "IF" statement run a macro, i.e.
=If("a"="a",RunMacroHere,"") ?

Thanks

You can't. A function can only return a value.

You could use event code to monitor a cell and then run the macro if it
takes a certain value


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H10")) Is Nothing Then
With Target
If .Value = "a" Then
myMacro
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rod" wrote in message
...
How can I have an "IF" statement run a macro, i.e.
=If("a"="a",RunMacroHere,"") ?

Thanks

I need to paste this if conditional into a few hundred cells. It would look
something like:
=if(and(j59<"",K59=""),RunMacro,"")
....
=if(and(j2059<"",K2059=""),RunMacro,"")
Does this make a difference for your recommendation?

Thanks

"Bob Phillips" wrote:

You can't. A function can only return a value.

You could use event code to monitor a cell and then run the macro if it
takes a certain value


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H10")) Is Nothing Then
With Target
If .Value = "a" Then
myMacro
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rod" wrote in message
...
How can I have an "IF" statement run a macro, i.e.
=If("a"="a",RunMacroHere,"") ?

Thanks

No, it makes not difference. A VBA macro that is called from a formula in a
worksheet cell can do NOTHING except calculate a result and return that result
to the cell containing the formula. i.e. it behaves the same as the built-in
functions like SUM, AVERAGE, etc.

To actually "run a macro" (what most people mean by that) requires an Event
macro.


On Thu, 17 Mar 2005 09:45:06 -0800, Rod wrote:

I need to paste this if conditional into a few hundred cells. It would look
something like:
=if(and(j59<"",K59=""),RunMacro,"")
...
=if(and(j2059<"",K2059=""),RunMacro,"")
Does this make a difference for your recommendation?

Thanks

"Bob Phillips" wrote:

You can't. A function can only return a value.

You could use event code to monitor a cell and then run the macro if it
takes a certain value


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H10")) Is Nothing Then
With Target
If .Value = "a" Then
myMacro
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rod" wrote in message
...
How can I have an "IF" statement run a macro, i.e.
=If("a"="a",RunMacroHere,"") ?

Thanks

Nope, how could it if a function cannot run a macro more cells won't affect
that, it just affects the answer

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("J59:J2059")) Is Nothing Then
With Target
If .Value <"" And .Offset(),1).Value = "" Then
myMacro
End IfK59:K2059"
End With
ElseIf Not Intersect(Target, Me.Range("K59:K2059")) Is Nothing Then
With Target
If .Value ="" And .Offset(0,-1).Value < "" Then
myMacro
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rod" wrote in message
...
I need to paste this if conditional into a few hundred cells. It would
look
something like:
=if(and(j59<"",K59=""),RunMacro,"")
...
=if(and(j2059<"",K2059=""),RunMacro,"")
Does this make a difference for your recommendation?

Thanks

"Bob Phillips" wrote:

You can't. A function can only return a value.

You could use event code to monitor a cell and then run the macro if it
takes a certain value


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H10")) Is Nothing Then
With Target
If .Value = "a" Then
myMacro
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rod" wrote in message
...
How can I have an "IF" statement run a macro, i.e.
=If("a"="a",RunMacroHere,"") ?

Thanks