Help please - how to run Solver in a protected sheet?



I would highly appreciate if someone could recommend a solution if it
is possible to run Solver in a protected worksheet.
The only solution I could think of was to create a separate button for
a user to unprotect the sheet and then sendkeys to call the Solver.
However, there is a problem after the Solver is run - the sheet is now
unprotected and I cannot find a solution how to protect it again.
I would be very glad if someone could share ideas.



jose luis

Try this to unprotect the sheet if it has password:

ActiveSheet.Unprotect Password:="abcdef"

Then protect the sheet again with:

ActiveSheet.Protect Password:="xxxxx", DrawingObjects:=True
Contents:=True, Scenarios:=False

I hope this helps.

See you,

jose lui


Thank you for your reply.
I tried what you suggested. However, my macro executes OK until it
calls the Solver. Solver solves the problem and then no code is
executed, which means that I cannot protect the sheet again, as the
macro stops after calling Solver and there is no obvious trigger to
link protection code after the Solver solves the problem.
Any ideas?

Ken Wright

One example:-

Sub Solvme()

Const PWORD As String = "abcdef"
Dim Sht1 As Worksheet
Set Sht1 = Worksheets("Sheet1")

With Sht1
.Unprotect Password:=PWORD

SolverOk SetCell:="$E$5", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$5"
SolverSolve (True)

.Protect Password:=PWORD
End With

End Sub


Thank you very much for your recommendation. This approach works
perfectly. The only thing is that I wanted to give a user complete
control over the Solver dialog box, so that it would be possible to
specify different Solver problems. Is it possible to do just that -
give a user a full access to the Solver and then protect the sheet
after the optimization process has been completed?

Thank you very much for your help.


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