worksheet change infinite loop/calculate for user functions

Hi, I inherited some worksheet and user function code. There are three
functions that seem to be executed only when the worksheet change event
occurs. They're all simple and basically the same - they turn a cell a color
based on other cell values. The first is in all the cells of a matrix very
much like a calendar with months on the x-axis and days on the y axis. The
"day" cells have function 1, the final column cells have function 2, and the
final row cells have function 3. When the user changes a value in a "day"
cell, the function changes the color, triggers a worksheet change event as
well as the aggregate col and row functions, which of course trigger more
worksheet change events. I have two problems (besides inheriting the code):

1. It looks like the only way he was able to trigger the secondary col and
row functions was by putting code in the worksheet change event. This seems
to cause a chain of infinitely looping events that Excel eventually kicks
out of. Sometimes it does it before all the functions execute, sometimes
not. Is there a better implementation? Is there some sort of equivalent to
calculate in vba? Is this what the volatile function is for?

2. When I paste (programmatically or manually) multiple "day" cells, only
the function of the first cell runs. I surmise this is the same problem as
above, but stepping through the code I get the <non Excel code> entry in the
stack trace so it's hard to find out what happened.

I don't have much time or license to rewrite very much. Is there an easy and
elegant solution to my problem(s)?

Many thanks,
Mike

To stop infinite triggers put
Application.EnableEvents = False
near the top and
Application.EnableEvents = True
near the bottom
--
Gary's Student


"Mike" wrote:

> Hi, I inherited some worksheet and user function code. There are three
> functions that seem to be executed only when the worksheet change event
> occurs. They're all simple and basically the same - they turn a cell a color
> based on other cell values. The first is in all the cells of a matrix very
> much like a calendar with months on the x-axis and days on the y axis. The
> "day" cells have function 1, the final column cells have function 2, and the
> final row cells have function 3. When the user changes a value in a "day"
> cell, the function changes the color, triggers a worksheet change event as
> well as the aggregate col and row functions, which of course trigger more
> worksheet change events. I have two problems (besides inheriting the code):
>
> 1. It looks like the only way he was able to trigger the secondary col and
> row functions was by putting code in the worksheet change event. This seems
> to cause a chain of infinitely looping events that Excel eventually kicks
> out of. Sometimes it does it before all the functions execute, sometimes
> not. Is there a better implementation? Is there some sort of equivalent to
> calculate in vba? Is this what the volatile function is for?
>
> 2. When I paste (programmatically or manually) multiple "day" cells, only
> the function of the first cell runs. I surmise this is the same problem as
> above, but stepping through the code I get the <non Excel code> entry in the
> stack trace so it's hard to find out what happened.
>
> I don't have much time or license to rewrite very much. Is there an easy and
> elegant solution to my problem(s)?
>
> Many thanks,
> Mike
>
>
>

Thank you. That's fixes part of my problem. Now, how do I force a Calculate
for the user defined functions?

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> To stop infinite triggers put
> Application.EnableEvents = False
> near the top and
> Application.EnableEvents = True
> near the bottom
> --
> Gary's Student
>
>
> "Mike" wrote:
>
>> Hi, I inherited some worksheet and user function code. There are three
>> functions that seem to be executed only when the worksheet change event
>> occurs. They're all simple and basically the same - they turn a cell a
>> color
>> based on other cell values. The first is in all the cells of a matrix
>> very
>> much like a calendar with months on the x-axis and days on the y axis.
>> The
>> "day" cells have function 1, the final column cells have function 2, and
>> the
>> final row cells have function 3. When the user changes a value in a "day"
>> cell, the function changes the color, triggers a worksheet change event
>> as
>> well as the aggregate col and row functions, which of course trigger more
>> worksheet change events. I have two problems (besides inheriting the
>> code):
>>
>> 1. It looks like the only way he was able to trigger the secondary col
>> and
>> row functions was by putting code in the worksheet change event. This
>> seems
>> to cause a chain of infinitely looping events that Excel eventually kicks
>> out of. Sometimes it does it before all the functions execute, sometimes
>> not. Is there a better implementation? Is there some sort of equivalent
>> to
>> calculate in vba? Is this what the volatile function is for?
>>
>> 2. When I paste (programmatically or manually) multiple "day" cells, only
>> the function of the first cell runs. I surmise this is the same problem
>> as
>> above, but stepping through the code I get the <non Excel code> entry in
>> the
>> stack trace so it's hard to find out what happened.
>>
>> I don't have much time or license to rewrite very much. Is there an easy
>> and
>> elegant solution to my problem(s)?
>>
>> Many thanks,
>> Mike
>>
>>
>>