Solver ignores constraints in protected sheets XL 2003


G

Guest

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.
 
Ad

Advertisements

D

Dana DeLouis

...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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top