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

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=26977

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


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"German" wrote in message
...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

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.