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.

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.