Calling a Macro within a cell

[brookc84]

I'm attempting to automate a macro within an “IF” and “AND” statement, so as not having to “click” on the macro to execute it after each data entry. I've attached two spreadsheets to illustrate. One works fine, and with the second one I'm attempting to create a formula in Column J so there is no need to “click” on the macro when I enter a number in Column J. When a number is entered in Column I, the macro deletes the value in Column D and moves all values 1 column to the left. A formula in Column B divides the sum of columns D-H by 5.

It appears OO can't find my macro (update) which is located in the basic library... under DocumentName-Standard-Module1. I've checked the spelling and it works fine when I “click” on the “Update Handicaps” macro button I created in the spreadsheet. Am I using incorrect syntax in Colmn J or is something else going on? The purpose of the second spreadsheet is to eliminate the need for the “Update Handicaps” macro button, and have the macro execute automatically when a number is entered in Column J.

Thank You

[brookc84]

Correction: The last sentence should read" The purpose of the second spreadsheet is to eliminate the need for the “Update Handicaps” macro button, and have the macro execute automatically when a number is entered in Column I."

[FJCC]

You can't call a macro Sub using its name in a cell. You can call a Function that way but a Function cannot change cells other than the one that called it, so that will not address your problem. What you want to do is to put a Listener on J1 that triggers the macro when the cell changes. A forum search for Modify Listener should find you some examples. You will have to write a macro to enable the listener when the file opens and a listener routine to trigger your existing macro when J1 changes.