| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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 wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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 wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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 wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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 wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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" wrote in message ... 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 wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
solver macros in protected worksheets
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" wrote in message ... 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 wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros, Forms, & Protected Worksheets..... how would I... | Setting up and Configuration of Excel | |||
| Macros with Protected Worksheets | Excel Discussion (Misc queries) | |||
| Group, Outline, Protected Worksheets and Protected Workbook | Excel Programming | |||
| Help please - how to run Solver in a protected sheet? | Excel Programming | |||
| Solver via VBA on protected sheet problem | Excel Programming | |||
Linear Mode
