Run a macro from within a function

[BK523]

I would like to launch a macro from within an "IF" statement.
Example: Cell C3 is a "yes / no" type cell. Cell C4 would evaluate the
contents of C3 and launch a macro if C3="Yes"

Can this be done?

 

[Mike H]

Hi,

Yes you probably can but no you can't simply call a macro with an if
statement. Some cell must change to make C3 switch between "Yes" & "no". so
you can use the worksheet change event to fire your macro.

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C3").Value <> "Yes" Then Exit Sub
'do things
End Sub

Mike

 

[Gord Dibben]

Formulas cannot run macros.

You could use event code to run a macro when C3 is changed to yes.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$C$3" And Target.Value = "yes" Then
Call macroname
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[BK523]

Thabnk you for your reply. It looks like this is a good approach & I will
take some time to develop it.

 

[PBezucha]

Just to prevent misapprehension:

A VBA FUNCTION can call a SUB. The function can be triggered by a change of
an argument, for example just your deciding yes/no (as Boolean variable). The
SUB can take values even from elsewhere, not only from the inner of the
function, but – there is the hell of restriction – it cannot pass any values
other than over this function output.

Remark: You are allowed to place a MsgBox into a function body, where you
can execute your deferred deciding step.

Regards

 

[Gord Dibben]

This is interesting.

Could you post something that would take care of OP's original question,
which was to call a macro from a formula in C4 that evaluates a yes/no in C3


Gord

 

[PBezucha]

Gord,

Your and Mike’s answers were sure the only straightforward answer to the
most probable content of OP’s question.

My last remark was only a hint, evoked by the form of MsgBox, in case the OP
was still hesitating how to tackle the task.

I must admit, with my basic sentence I could confuse anybody. It depends how
you define the term of macro. If it is a procedure Sub() with empty brackets,
which is appearing in the Macro bar, then you certainly can, by means of it,
set any variable on the module level, without the claim to trigger further
changes dependent on this variable.

Dim sText As String
Sub TextYes()
sText = "Yes"
End Sub

Sub TextNo()
sText = "No"
End Sub

Why not to use it now in a function:
Function SayYesOrNo(YesNo As Boolean) As String
If YesNo Then TextYes Else TextNo
SayYesOrNo = sText ‘or anything
End Function

Why to build and run such mutilated macros separately? For example for
intermediate changing the flag (shortkey!), previously set in running the
Function, before running another macro
Sub DoSomething()
… If sText = "No" Then ……..

Of course, we talk still about the changing of some variables, no other
activity, as I was stressing in my text.

Playing with module level variables may be sometimes useful (there may be
even such cases) but fulminately dangerous, owing to the loss of control with
a bad fixing.

I apologize for the discussion that was evidently out of the topic. Take it
as a slip of brain.

Petr