Run a macro in a formula (Windows NT)

[johno1]

Hi
Could you give me some help please
is it possible to run a macro through a formula for example
=IF(A5=3,Application.Run"Macro 1",NO) or something like this, this did not
work when I tried it.
Any help would be much appreciated.
thanks Jonathon

[pieterse]

No, that is not possible. You can use the Worksheet_change event to run a macro when anything has changed to a worksheet.


- rightclick on the sheet Tab and choose "View Code".
- in the window that you get, from the lefthand dropdown select "Worksheet", from the righthand select "Change"

You'll see these lines appear:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

Between these two lines, paste this code:

Application.EnableEvents = False
If Intersect(Target, [a1:b2]) Is Nothing Then
Exit Sub
End If
Target.Value = -Target.Value
Application.EnableEvents = True

Close the Visual basic editor.

The example above negates all entries done into the range A1:B2 and does nothing in all other cells.

[LegareColeman]

No, you can not run a macro from a formula. You can run a User Defined Function if that can do what you need to have done. If not, then you would need to use one of the event routines (Worksheet Change might be a good choice in this case) to run the macro. The macro would then have to do the IF in VBA and then do whatever you need to do.

[johno1]

Thanks for the help
I did not think that it was possible in a formulas but thanks for the help I will have to learn more about Worksheet_change event I appreciate the help

Thanks jonathon

[ralphad]

Jonathon,

Simply change your Macro from being a Sub to being a Function, and make it return a value. The return value can be whatever you like, but since you've used "NO", in the worksheet function, I'll make it return "YES".

e.g.<pre>Sub Macro1
.
.
End Sub</pre>

becomes

<pre>Function Macro1
.
.
Macro1="YES"
End Function</pre>


Then, change your formula to

<pre>=IF(A5=3,Macro1(),"NO") </pre>

If A5=3, your macro should run, and the cell should say "YES". If A5<>3, then the macro won't run and the cell will say "NO".

NOTE: There are certain things that VBA in your Macro can't do if it's being called in this way, from a worksheet function. e.g. change values of other cells, add sheets, etc. In general, anything that alters the appearance of the workbook, other than the calling cell's value, can't be done.

[johno1]

Adam

I tried the function macro and the formula returned yes but the macro did not run for example this is what I wont the macro to do
e.g.
Function Macro1
.
' Macro1 Macro
' Macro recorded 21/03/2002 by Office Automation
'
Range("C4").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Macro1="YES"
End Sub

Is there any way to achieve this?
Thanks for the help
Jonathon

[LegareColeman]

No, everything in that procedure is invalid in a function.

[ralphad]

The only way to achieve this, is to use the Worksheet_Change event to run the macro, as was suggested before.

Change Macro1 back to a Sub, and put the following code in the worksheet's code module.
<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("a5")) Is Nothing Then
If Range("a5").Value = 3 Then Macro1
End If
End Sub</pre>


And, ff you still want the formula to display YES or NO, just change it to a simple
<pre>=IF(A5=3,Application.Run"Macro 1",NO)</pre>