Using solver function in a locked (protected) worksheet

[Niels]

I have made a worksheet where I am using the solver function to optimize a
blend of products. This works perfect. Now I want to lock (protect) some
areas of the worksheet, but when doing so the solver function will not work.

Following translated message arrives:
The active document is not a worksheet, is protected or shared.

Hope someone can help me.

Best ragards,

Niels

 

[Squeaky]

Hi Niels,

I have never used solver before but I have some sugestions you may try.
First, try and unlock the cells that solver references by right clicking the
cell, selecting Format Cells, then the Protection tab. Uncheck "Locked".
Protect the sheet and see if the error comes up.

If it does, you can write a macro that will unprotect your worksheet, run
the solver, then reprotect the worksheet. Use the Macro recorder to do this,
then make an icon for your toolbar and assign the macro to it for easy
access. Another way is to have a macro that simply protects and unprotects,
and you can run the solver when you need to.

Let me know if any of this helps or if you need macro help.

Squeaky

 

[Dana DeLouis]

Hi. Solver needs to adjust cells on the worksheet.
I believe that when Solver runs, one of the first things it does is
check to make sure the worksheet in not protected. It does not have
enough logic to check if only the changing cells are unlocked.
I would suggest to unprotected the worksheet.
Or perhaps make an unprotected copy of the worksheet via vba, and run
solver on that.
= = = = =
Dana DeLouis