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

[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

 

[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

 

[German]

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")

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

 

[German]

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.