Execute Macros From Formulas (Or Cell Value)

indi visual · #1 (**permalink**) 10-25-10, 19:28

I would like to execute macros if specific cells change.

I have 25 cells I would like to apply this to.

After hours upon hours of failure, I would really just like to know if this even possible.

If "Z1" equals "1", then run macro
If value is not "1" do nothing
If "Z2" equals "2", then run macro
If value is not "2" do nothing

Colin Legg · #2 (**permalink**) 10-26-10, 04:40

You need to use event handlers to do this.

If the cells contain formulas then you need to use the Worksheet_Calculate() event handler.
If the cells contain constants then you need to use the Worksheet_Change() event handler.

The Event Handler will run everytime the respective event is raised but you can build conditional logic within the procedure so that it effectively "does nothing".

Nielsje · #3 (**permalink**) 03-03-11, 14:22

Open the Visual Basic Editor and add the following code lines to the specific sheet:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" And Range("A1").Value = 1 Then MsgBox "I'm a macro"

If Target.Address = "$A$2" And Range("A2").Value = 2 Then MsgBox "I'm another macro"

End Sub

Result:

Only if the value of cell A1 changes to 1, Excel displays a Msgbox.
Only if the value of cell A2 changes to 2, Excel displays another Msgbox.

I guess this is what you want.

Hope this helps..

Internet Services

Web Development

Digital Marketing

Consumer Tech