Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application is already Calculating during a "Change" event
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application is already Calculating during a "Change" event
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 "Dr Rubick" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application is already Calculating during a "Change" event
Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to
ignore it, just line the OnTime and Wait methods. "Jim Thomlinson" wrote: 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 "Dr Rubick" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application is already Calculating during a "Change" event
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 "Dr Rubick" wrote: Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to ignore it, just line the OnTime and Wait methods. "Jim Thomlinson" wrote: 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 "Dr Rubick" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application is already Calculating during a "Change" event
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. "Jim Thomlinson" wrote: 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 "Dr Rubick" wrote: Thanks for the suggestion. Unfortunately, it did not work. Excel seemed to ignore it, just line the OnTime and Wait methods. "Jim Thomlinson" wrote: 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 "Dr Rubick" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"change" event for validation/pick lists | Excel Programming | |||
"Dragging Chart Markers to Change Data" Event | Excel Programming | |||
Is there a "cell change" event? | Excel Programming |