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

  • Thread starter Thread starter German
  • Start date Start date
G

German

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.

Thanks,

Excel-learner
 
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?
 
One example:-

Sub Solvme()

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

SolverReset
With Sht1
.Unprotect Password:=PWORD

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

.Protect Password:=PWORD
End With
SolverReset

End Sub
 
Ken;
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.

German.
 
Back
Top