Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting worksheet, using solver, and re-protecting
Hi,
I am trying to create a macro that allows me to: 1) Unprotect worksheet 2) Run solver (criteria is already defined and stored in solver) 3) Protect worksheet. I recorded a macro to do this and have added SOLVER to my VBA preferences but am still receiving an error when I run the macro. My code is below. Any guidance would be much much appreciated. Sub optimize() ' ' optimize Macro ' This allows you to unprotect the worksheet, run solver, and re-protect the workshet. ' ' ActiveSheet.Unprotect SolverOk(SetCell:="$K$82",MaxMinVal:="1",ValueOf:= "0",ByChange:"$B$65:$I$65") SolverSolve userFinish = True ActiveWindow.SmallScroll Down:=51 ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:= _ True, AllowUsingPivotTables:=True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting worksheet, using solver, and re-protecting
Hi, this doesn't help with the solver bit, but if you add
'UserInterfaceOnly:=True' to a protect statement for the worksheet, you will no longer need to do the unprotect / protect thing. It will mean that macros are free to change anything on the sheet, while the user is still restricted. Cheers, On Apr 2, 3:18*pm, Vlookup help wrote: Hi, I am trying to create a macro that allows me to: 1) Unprotect worksheet 2) Run solver (criteria is already defined and stored in solver) 3) Protect worksheet. I recorded a macro to do this and have added SOLVER to my VBA preferences but am still receiving an error when I run the macro. My code is below. Any guidance would be much much appreciated. Sub optimize() ' ' optimize Macro ' This allows you to unprotect the worksheet, run solver, and re-protect the workshet. ' ' * * ActiveSheet.Unprotect SolverOk(SetCell:="$K$82",MaxMinVal:="1",ValueOf:= "0",ByChange:"$B$65:$I$65*") * * SolverSolve * * userFinish = True * * ActiveWindow.SmallScroll Down:=51 * * ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ * * * * False, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:= _ * * * * True, AllowUsingPivotTables:=True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting and unprotecting | Excel Worksheet Functions | |||
Protecting & Unprotecting worksheet | Excel Programming | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) | |||
Protecting/Unprotecting Workbook | Excel Programming | |||
Protecting & unprotecting worksheets | Excel Programming |