MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Jo on January 18, 2002 4:28 AM

Would really like to be able to use an IF statement to determine which of two macros to run when the contenets of a given cell changes. Ideas please


Posted by DK on January 18, 2002 4:44 AM

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

Posted by Mudface on January 18, 2002 4:59 AM

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.

Posted by faster on January 18, 2002 5:48 AM

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. . .

Posted by Caliban on January 18, 2002 5:51 AM

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

Posted by faster on January 18, 2002 5:55 AM

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

Posted by Caliban on January 18, 2002 6:03 AM

I didn't think so ....

Posted by Caliban on January 18, 2002 6:07 AM

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?

Posted by faster on January 18, 2002 6:10 AM

Function does return msgbox and makes call

Posted by Caliban on January 18, 2002 6:11 AM

No it doesn't (nt)

Posted by faster on January 18, 2002 6:18 AM

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


Posted by Caliban on January 18, 2002 6:23 AM

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.

Posted by Caliban on January 18, 2002 6:28 AM

PS .....


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

Posted by faster on January 18, 2002 6:35 AM

Good Point

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

Posted by Caliban on January 18, 2002 6:42 AM

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)

Posted by faster on January 18, 2002 6:55 AM

Re: Good Point

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

Posted by Jo on January 21, 2002 12:28 AM

Many Thanks Folks!

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