Application is already Calculating during a "Change" event

[Guest]

I have a workbook with a "change" event, which updates the formats of other
cells in the workbook. For example, another cell may turn red to indicate
that its value is no longer valid with the new value of the changed cell.

Another workbook links to this workbook, but whenever it is open and
automatic calculation is on, my "on change" macro crashes. I've found that I
can tell when this is going to happen with the "Application.CalculationState"
property. If this property is set to xlCalculating, then the macro is going
to crash and I could bail out, but I still need this macro to complete. I've
already tried Application.Wait and Application.OnTime, but Excel seems to
ignore these when the CalculationState property equals xlCalculating.

According to the call stack, the user definded function in the file that
links to mine is the first thing called, then "[<Non-Basic Code>]", then my
change event. Is there any way to force the calculation to complete before
continuing, or pause the macro and allow it to complete, or call the logic of
my change event through a different event?

 

[Guest]

I am not sure if...

DoEvents

will do the trick but it might be worth a try. It holds execution until
external proccesses are complete.

HTH

 

[Guest]

Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to
ignore it, just line the OnTime and Wait methods.

I am not sure if...

DoEvents

will do the trick but it might be worth a try. It holds execution until
external proccesses are complete.

HTH

I have a workbook with a "change" event, which updates the formats of other
cells in the workbook. For example, another cell may turn red to indicate
that its value is no longer valid with the new value of the changed cell.

Another workbook links to this workbook, but whenever it is open and
automatic calculation is on, my "on change" macro crashes. I've found that I
can tell when this is going to happen with the "Application.CalculationState"
property. If this property is set to xlCalculating, then the macro is going
to crash and I could bail out, but I still need this macro to complete. I've
already tried Application.Wait and Application.OnTime, but Excel seems to
ignore these when the CalculationState property equals xlCalculating.

According to the call stack, the user definded function in the file that
links to mine is the first thing called, then "[<Non-Basic Code>]", then my
change event. Is there any way to force the calculation to complete before
continuing, or pause the macro and allow it to complete, or call the logic of
my change event through a different event?

 

[Guest]

How about using the on_calculate event. If calcualtion is cuasing the problem
maybe you cna use the on_calacualte event to fix it... WAG

Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to
ignore it, just line the OnTime and Wait methods.

I am not sure if...

DoEvents

will do the trick but it might be worth a try. It holds execution until
external proccesses are complete.

HTH

I have a workbook with a "change" event, which updates the formats of other
cells in the workbook. For example, another cell may turn red to indicate
that its value is no longer valid with the new value of the changed cell.

Another workbook links to this workbook, but whenever it is open and
automatic calculation is on, my "on change" macro crashes. I've found that I
can tell when this is going to happen with the "Application.CalculationState"
property. If this property is set to xlCalculating, then the macro is going
to crash and I could bail out, but I still need this macro to complete. I've
already tried Application.Wait and Application.OnTime, but Excel seems to
ignore these when the CalculationState property equals xlCalculating.

According to the call stack, the user definded function in the file that
links to mine is the first thing called, then "[<Non-Basic Code>]", then my
change event. Is there any way to force the calculation to complete before
continuing, or pause the macro and allow it to complete, or call the logic of
my change event through a different event?

 

[Guest]

It seems like all of the calculation is being done by the workbook that links
to the one with the Change event. When I add an on_calculate event to the
original file, my event is never called.

How about using the on_calculate event. If calcualtion is cuasing the problem
maybe you can use the on_calacualte event to fix it... WAG

Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to
ignore it, just line the OnTime and Wait methods.

I am not sure if...

DoEvents

will do the trick but it might be worth a try. It holds execution until
external proccesses are complete.

HTH

:

I have a workbook with a "change" event, which updates the formats of other
cells in the workbook. For example, another cell may turn red to indicate
that its value is no longer valid with the new value of the changed cell.

Another workbook links to this workbook, but whenever it is open and
automatic calculation is on, my "on change" macro crashes. I've found that I
can tell when this is going to happen with the "Application.CalculationState"
property. If this property is set to xlCalculating, then the macro is going
to crash and I could bail out, but I still need this macro to complete. I've
already tried Application.Wait and Application.OnTime, but Excel seems to
ignore these when the CalculationState property equals xlCalculating.

According to the call stack, the user definded function in the file that
links to mine is the first thing called, then "[<Non-Basic Code>]", then my
change event. Is there any way to force the calculation to complete before
continuing, or pause the macro and allow it to complete, or call the logic of
my change event through a different event?