Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver ignores constraints in protected sheets XL 2003
Hi,
I have a simple VB program (sub) that uses solver to optimize a single variable. The program allows the user to input constraints for max/min bounds. When the worksheet is unprotected it runs fine, however, when the sheet is protected the solver no longer stays within the specified max/min constraints... Sub Optimize() If Range("E4") = "Shear" Then Range("I3") = Range("C5") SolverReset SolverOk SolverAdd CellRef:="I3", Relation:=1, FormulaText:="C4" SolverAdd CellRef:="I3", Relation:=3, FormulaText:="C5" SolverOk SetCell:="I5", MaxMinVal:=2, ByChange:="I3" SolverSolve UserFinish:=True SolverFinish ElseIf Range("E4") = "Moment" Then Range("I3") = Range("C5") SolverReset SolverOk SolverAdd CellRef:="I3", Relation:=1, FormulaText:="C4" SolverAdd CellRef:="I3", Relation:=3, FormulaText:="C5" SolverOk SetCell:="I4", MaxMinVal:=2, ByChange:="I3" SolverSolve UserFinish:=True SolverFinish End If End Sub What needs to be done to ensure that the solver solution stays within the constrained bounds, even when the sheet is protected? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver ignores constraints in protected sheets XL 2003
...when the sheet is protected the solver no longer stays within
the specified max/min Hi. I believe one of the test Solver looks at is "ActiveSheet.ProtectContents." If True, then Solver would not be able to change any of the "Changing Cells." I believe this is one of the reasons. -- HTH :) Dana DeLouis Windows XP & Office 2007 "adamaagard" wrote in message ... Hi, I have a simple VB program (sub) that uses solver to optimize a single variable. The program allows the user to input constraints for max/min bounds. When the worksheet is unprotected it runs fine, however, when the sheet is protected the solver no longer stays within the specified max/min constraints... Sub Optimize() If Range("E4") = "Shear" Then Range("I3") = Range("C5") SolverReset SolverOk SolverAdd CellRef:="I3", Relation:=1, FormulaText:="C4" SolverAdd CellRef:="I3", Relation:=3, FormulaText:="C5" SolverOk SetCell:="I5", MaxMinVal:=2, ByChange:="I3" SolverSolve UserFinish:=True SolverFinish ElseIf Range("E4") = "Moment" Then Range("I3") = Range("C5") SolverReset SolverOk SolverAdd CellRef:="I3", Relation:=1, FormulaText:="C4" SolverAdd CellRef:="I3", Relation:=3, FormulaText:="C5" SolverOk SetCell:="I4", MaxMinVal:=2, ByChange:="I3" SolverSolve UserFinish:=True SolverFinish End If End Sub What needs to be done to ensure that the solver solution stays within the constrained bounds, even when the sheet is protected? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver Ignores Constraints | Excel Discussion (Misc queries) | |||
Solver Constraints | Excel Discussion (Misc queries) | |||
Excel Solver constraints | Excel Worksheet Functions | |||
Solver: Can't add constraints when they are above/below certain va | Excel Programming | |||
Solver Constraints | Excel Discussion (Misc queries) |