Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
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 "BK523" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
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 On Tue, 13 Jan 2009 11:38:06 -0800, BK523 wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
Thabnk you for your reply. It looks like this is a good approach & I will
take some time to develop it. "Gord Dibben" wrote: 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 On Tue, 13 Jan 2009 11:38:06 -0800, BK523 wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
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 -- Petr Bezucha "BK523" wrote: Thabnk you for your reply. It looks like this is a good approach & I will take some time to develop it. "Gord Dibben" wrote: 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 On Tue, 13 Jan 2009 11:38:06 -0800, BK523 wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
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 On Wed, 14 Jan 2009 02:26:05 -0800, PBezucha wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a macro from within a function
Gord,
Your and Mikes answers were sure the only straightforward answer to the most probable content of OPs 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 -- Petr Bezucha "Gord Dibben" wrote: 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 On Wed, 14 Jan 2009 02:26:05 -0800, PBezucha wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro with sum function | Excel Worksheet Functions | |||
Using macro or other function | Excel Discussion (Misc queries) | |||
Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
macro or function | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming |