Starting a macro from a formula?

You can, but what exactly are you trying to accomplish? You can run into some bad stuff if you do it incorrectly...

EG
I just want to insert a blank row when the value in a sorted column changes from 2001 to 2002 in a sorted table

if (a2<>a1,personal.xls!InsertRow

I can see this causing problems - like when the row is inserted, the 2 cells still won't be equal. I suggest that you just write a macro and attach it to a command button or something.

However, since you asked, you can (at your own peril) call a function from a user-defined function.

Hope this helps,

Russell

I understand your concern however I wasn't explicit enough. The formula should read
if (sheet1!a2<>sheet1!a1,personal.xls!InsertRow
where the formula is on sheet2

How do I create a user defined function and call it from a formula. I created the InsertRow macro by recording it
Sub InsertRow()
'
' InsertRow Macro
Application.CutCopyMode = False
Selection.EntireRow.Insert
End Sub I can see this causing problems - like when the row is inserted, the 2 cells still won't be equal. I suggest that you just write a macro and attach it to a command button or something. However, since you asked, you can (at your own peril) call a function from a user-defined function. Hope this helps, Russell

You won't be able to insert a row using a UDF called from a cell. Excel forbids a UDF from altering the structure of the workbook. All they can do is return a value to the cell that called them.

Perhaps you could call your macro from a command button or use the Worksheet_Change event to test whether sheet1!a2<>sheet1!a1 and then call your macro.

Gary I understand your concern however I wasn't explicit enough. The formula should read InsertRow Macro