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" wrote in message
...
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