solver macros in protected worksheets

[Guest]

I've succesfully built a macro that run 2 solver commands in 2 separate tabs
of the same workbook. However, If I protect the worksheets with passwords,
the solver macros trigger an error message - error in parameters or
contraints. It works if I unprotect and then protect within the macro, but
cannot use it if I really want to put a password. Any alternatives?

 

[Tim Williams]

Why does this not work with a password?
You can unprotect/reprotect using the password in the same way as with no
password.

Or unlock just the cells Solver needs to change.

Tim

 

[Guest]

Exactly !! Why does this not work? I already tried unlocking cells.
More information may help you find me a solution. The Calcs worksheet has
circular references set with iterations in the calculation options.
This is how the macro looks now.

Sub Macro10()
'
' Macro10 Macro
' Macro recorded 2/16/2006 by Hernandege
'

'
Application.Run "Solver.xla!Auto_Open"
Sheets("Calcs").Select
ActiveSheet.Unprotect
Sheets("INPUT").Select
ActiveSheet.Unprotect
Range("C6").Select
ActiveCell.FormulaR1C1 = "10000"
Sheets("Calcs").Select
Range("A200").Select
SolverOk SetCell:="$C$97", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$89"
SolverSolve
Range("A200").Select
Sheets("INPUT").Select
SolverOk SetCell:="$C$38", MaxMinVal:=1, ValueOf:="0.549999",
ByChange:="$C$6"
SolverSolve
Sheets("INPUT").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("Calcs").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("INPUT").Select
Range("C6").Select
ActiveWindow.SmallScroll Down:=25
End Sub

 

[Tim Williams]

Your original message said it worked if you unprotected the sheet
within the macro. Using a password is just one additional parameter to
the unprotect/protect methods.

Note you don't have to select a sheet to unprotect it

Sheets("Calcs").Select
ActiveSheet.Unprotect

could just be

Sheets("Calcs").Unprotect


Tim

 

[Guest]

Thanks Tim. But how do I include a password so nobody else can unprotect it
later?

 

[Tim Williams]

Activesheet.Protect Password:="YourPassword"

etc.

Since the password is now hard-coded in the VBA project you should also
password-protect that also (though be aware this is protection only against
"casual" interest....)

 

[Guest]

Thanks again.
I'm still curious why the twin solver did not work while protected?
It did when I only had one worksheet solver coded.