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