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
|