Running macros off IF statements when a cell changes????

Hello,

Calling a macro from an IF statement isn't possible. A function can only return a value to the cell that it has been input to.

If you want to run separate macros you could use the Worksheet_Change event. Right click the worksheet tab and choose View Code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value <= 100 Then
Macro1
Else
Macro2
End If
End Sub

This will run Macro1 if the value in A1 is less than or equal to 100, else it will run Macro2.

HTH,
D

You could use the Worksheet_Change event to do something like this: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim x
Dim y
x = Target.Row
y = Target.Column

If Not x = 1 And Not y = 1 Then Exit Sub ' ie if it isn't cell A1 that hasn't been changed

If Target.Value= yourcondition Then Call Sub1
If Target.Value= yourcondition2 Then Call Sub2

End Sub

Where Sub1 and Sub2 are your macros. Probably clumsy, but it should work OK.

You can make a call with an if statement.

You can make a call with an if statement.

Cell A1 = 1
Formula in Cell B2 = =IF(A1=1,MyFunction(),"NoCall")

Place this Function in your module.

Function MyFunction()
MsgBox "Got Here"
'any code or call here
End Function

have fun. . .

But you can't call a macro! (nt)

I thought the call was obvious

Change module code to this.

Function MyFunction()
MsgBox "Got Here"
'any code or call here
MyFunction = "Call Made"
Call MyMacro
End Function

Sub MyMacro()
MsgBox "You have called a macro"
End Sub

I didn't think so ....

I didn't think it was .....

A function can't produce a message box and such like. It can only return a result.
Have you tried your suggestion?

Function does return msgbox and makes call

No it doesn't (nt)

I'll leave it to the board.

You can make a call with an if statement. Changing the
value in cell A1 to 1 invokes MyFunction which calls
MyMacro. Stop saying "can't" and try it.

Cell A1 = 1
Formula in Cell B2 = =IF(A1=1,MyFunction(),"NoCall")

Place this Function in your module.

Function MyFunction()
MsgBox "Got Here"
'any code or call here
MyFunction = "Call Made"
Call MyMacro
End Function

Sub MyMacro()
MsgBox "You have called a macro"
End Sub

Sorry, maybe I should expand a bit ......

..... yes, your code will produce the message boxes, but try doing something else.
For instance, change your macro to :-

Sub MyMacro()
Range("B1").value =1
End Sub

It won't work - because a function cannot change the Excel environment, it can only return a result.

PS .....

..... a function can work like a macro if it is called by a macro, but this is not the scenario.

Good Point

The function and macro are called but are not fully
functional. Interesting.

Re: Good Point

Just the basic Excel rule :-

Functions - can only return results
Macros - can return results and/or make changes to the Excel environment (i.e. can do anything)

Re: Good Point

I can't find any way to make use of the call beyond
msgbox, inputbox. . . Thanks for the info.

Many Thanks Folks!

Many Thanks for all your help. Plenty here to get me going again!!