Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculation during Change Event
I need to run Solver every time a few cells are changed. I have figured out
that to do this the Calculation Property must be set to manual otherwise the solver will dump. However, when the select cells aren't being changed, I need automatic calculating. The Worksheet_change event that I wrote is included below, but for some reason it is incapable of setting the Calculation to Manual and later back to automatic. Thus, my event dumps every time. Why is this happening and what can I do about it? --------------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Stop If Not (Target.Address = "$B$2" Or Target.Address = "$B$3" Or Target.Address = "$B$4" Or _ Target.Address = "$B$5" Or Target.Address = "$B$6" Or Target.Address = "$B$9" Or _ Target.Address = "$B$10" Or Target.Address = "$B$11" Or Target.Address = "$B$12") Then MsgBox Target.Address Stop Exit Sub End If Call preProcess ' Adjust Fit Type (if needed) If Range("b2").Value 30 Then Range("b3").Value = "Fillet Root Side" ' Adjust Pitch (if needed) If Range("b2").Value = 45 Then If Range("b4").Value < 10 Then Range("b4").Value = 10 Else If Range("b4").Value 48 Then Range("b4").Value = 48 If Range("b3").Value = "Flat Root Major Diameter" And Range("b4").Value 16 Then _ Range("b4").Value = 16 If Range("b3").Value = "Flat Root Major Diameter" And Range("b4").Value < 3 Then _ Range("b4").Value = 3 End If ' Adjust Number of Teeth (if needed) Call reCalc If Range("b2").Value = 45 Then If Range("b5").Value < 11 And Range("b4").Value 48 Then Range("b5").Value = 11 If Range("b5").Value Sheets("Charts").Range("e42").Value And _ Range("b4").Value 48 Then Range("b5").Value = Sheets("Charts").Range("e42").Value Else If Range("b5").Value 60 Then Range("b5").Value = 60 End If ' Adjust Class (if needed) If Range("b3").Value = "Flat Root Major Diameter" Then Range("b6").Value = 5 Call reCalc ' Calculate Inverse Involute and Sevolute Functions Call inverseFunctions Call reCalc Call postProcess End Sub -------------------------------------------------------------------------- Thanks. Pflugs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Launch Click Event Using Application.Run | Excel Discussion (Misc queries) | |||
Application level event help | Excel Discussion (Misc queries) | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Application is already Calculating during a "Change" event | Excel Programming | |||
Application Event - Opening Workbook | Excel Programming |