solver macros in protected worksheets

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?

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

--
Tim Williams
Palo Alto, CA


"Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in message
news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> 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?

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" wrote:

> 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
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in message
> news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> > 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?
>
>
>

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


Gilbert LAC wrote:
> 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" wrote:
>
> > 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
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in message
> > news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> > > 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?
> >
> >
> >

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

"Tim Williams" wrote:

> 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
>
>
> Gilbert LAC wrote:
> > 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" wrote:
> >
> > > 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
> > >
> > > --
> > > Tim Williams
> > > Palo Alto, CA
> > >
> > >
> > > "Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in message
> > > news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>

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

--
Tim Williams
Palo Alto, CA


"Gilbert LAC" <GilbertLAC@discussions.microsoft.com> wrote in message
news:DDCCF5BA-5A41-4FA6-8561-786AAA85FD0C@microsoft.com...
> Thanks Tim. But how do I include a password so nobody else can unprotect
it
> later?
>
> "Tim Williams" wrote:
>
> > 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
> >
> >
> > Gilbert LAC wrote:
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Tim Williams
> > > > Palo Alto, CA
> > > >
> > > >
> > > > "Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in
message
> > > > news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >

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.

"Tim Williams" wrote:

> 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....)
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Gilbert LAC" <GilbertLAC@discussions.microsoft.com> wrote in message
> news:DDCCF5BA-5A41-4FA6-8561-786AAA85FD0C@microsoft.com...
> > Thanks Tim. But how do I include a password so nobody else can unprotect
> it
> > later?
> >
> > "Tim Williams" wrote:
> >
> > > 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
> > >
> > >
> > > Gilbert LAC wrote:
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > Tim Williams
> > > > > Palo Alto, CA
> > > > >
> > > > >
> > > > > "Gilbert LAC" <Gilbert LAC@discussions.microsoft.com> wrote in
> message
> > > > > news:B2DDC801-164D-4106-9BFA-16B918AB7CEE@microsoft.com...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>
>