If "true" run a Macro

[Rod]

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

Thanks

 

[Bob Phillips]

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]

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

 

[Myrna Larson]

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.

 

[Bob Phillips]

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)