UDF won't launch macro

[hdf]

I have created a simple User Defined Function that I had hoped would
launch a specific macro depending on whether the value in a given cell
using the UDF was True or False.

It is not working, so I'm obviously doing something wrong.

When the value the UDF is linked to is FALSE, the UDF cell puts in the
value "0" (which is fine for my needs). But when the value is linked
to the formula is TRUE, the UDF cell gives back the #Value! error and
it does not call the macro I want it to call.

Can someone help explain what I'm doing wrong? Following is the UDF
code I used.
_________

Function LaunchMacro (InputValue As Boolean)

If InputValue = True Then

Application.Run "NamedMacro"

Else

Exit Function

End If

End Function
_________

 

[tissot.emmanuel]

Hi,

Don't spend time in trying to do that, it's impossible. The purpose of an
UDF is to return a value to the calling cell, nothing else, anyway you try.

Best regards from France,

Manu/

 

[Bob Phillips]

You need event code for that.

What is the condition and what to launch?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hdf]

Bob,

I'm not sure what you mean by event code.

If I understand your questions, the condition is when the value of a
given cell input as the variable of my new UDF = TRUE launching (or
trying to) a macro that clears and repopulates a named range, as
follows:

cell: A1 = True
cell: A2 =Launchmacro(a1)

If it worked like I had intended it to, when A1 = TRUE it would run
the macro code below. I really don't care what value is put into cell
A2 since my objective is to get the model to run the macro when
A1=TRUE
__________

Sub Reset_Rev5_Col_2_TypeB()

Range("test_clear") = Clear
With Range("test_clear")
.Rows(1).Select
.value = 0
.Font.ColorIndex = 5
End With

With Range("test_clear_col")
.FormulaR1C1 = "=r[-1]c"
.Font.ColorIndex = 1
End With

Application.Goto Reference:="Start"


End Sub
_________

 

[Bob Phillips]

This is all you need then. It will run the macro if you select cell A2 and
A1 is True

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1") Then Call Reset_Rev5_Col_2_TypeB
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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hdf]

Bob,

Thanks for your help. I must be doing something wrong, though. Did
you suggest that I simply plug in code in the WS event as you suggest
and ignore the code I created in the standard code module or are the
two sets of code suppose to work in tandem.

If I simply plug in the code you recommended in a WS event, and ignore
my code, it goes what appears to be a loop with the screen flickering
until it seems to get tired and quit.

If I put =LaunchMacro(A1) in cell A2 and put your recommended code in
the WS event, it does the same or the debug error box comes up.

What I'm trying to do is the following:

I have a single cell range called COMFIRM. In this range there is a
standard IF function that will give a TRUE or FALSE answer depending
on other calculations in my worksheet.

Based on the results of CONFIRM, when TRUE, I would like to launch
the code in the macro called Reset_Rev5_Col_2_TypeB

Thanks again for you help.

 

[Bob Phillips]

Sorry, I messed up my code. It should be

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Call Reset_Rev5_Col_2_TypeB
End If
End Sub

I didn't test against your code, but you should keep that macro. But I did
notice one thing. Where is the range Start, not A2 I hope.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hdf]

Bob,

Thanks. I will test the new code and let you know how it went.

No, START is a range somewhere else on the worksheet.

 

[hdf]

Bob,

Thanks for the help.

I was not able to get it to work as planned. It works, in that if you
select cell A2 with the cursor it launches the macro, but I could not
get it to work so that if A2 content was TRUE (without selecting it,
simply based on whether the cell A2 had the value "TRUE" in it, as
opposed to "FALSE") it would launch the macro code.

I will find another approach to solve my overall problem rather than
trying to launch a macro based on the results of a function - which
appears to not be possible from all other comments I've read in
researching this issue.


Again, thank you.