Solver via VBA on protected sheet problem

[josvr]

Hi
I'm writing an excel + VBA application that uses the
solver add in. It works fine as long as the sheet on which
the target and variable cells are, is unprotected. When I
protect the sheet the solver seems not to do anything.
There are no error messages

I use the following calls to load the model and to run the
solver:
Call Application.Run("Solver.xla!SolverLoad", "N207:N211")
Call Application.Run("Solver.xla!SolverSolve", True, False)

I use these run calls instead of the direct solver
function calls to avoid a compiler error if the reference
to the solver add-in is not set in the VB environment, but
the direct function calls seem to behave similar.

Does anyone know this problem (nothing mentioned in the MS
knowledge base)?
Is there a workaround?
Thank in advance
Regards
Jos

 

[merjet]

Put before the Solver call: ActiveSheet.Unprotect "pwd"
Put after the Solver call: ActiveSheet.Protect Password:="pwd", _
DrawingObjects:=True, _
Contents:=True,
Scenarios:=True

HTH,
Merjet

 

[josvr]

Thanks for your suggestion but for me this is not a valid
option. It is the intention to protect the worksheet only
and not the workbook. It will be not too difficult to
access the VB code of the protected sheet (just copy the
sheet to a blank workbook) and that will publish the
password.
Any other suggestions?

 

[Dana DeLouis]

I don't believe Solver will work with the sheet protected. Try running
Solver manually with the protected sheet active.
You should get a message saying the sheet is protected.
I get the impression that it is of "high" importance not to be protected...

Sub ThisIsWhatYouShouldGet()
MsgBox [[SOLVER.XLA]Language!A2]
End Sub

This is triggered from the DialogBox. With VBA, you are bypassing this
initial dialog box. Solver is not coded (in Excel XP) to scan for
protection status on each applicable cell. It takes the easy way and just
checks the protection status of the sheet.

However, Solver is set to check the protection status each time you use vba
to add / change the model. (constraints...etc) However, you are bypassing
all of Solver's checking by using the "Load" command. Solver does no
checking on the protection of the sheet at this time. I am not clear as to
what happens (err return codes) when you get the Solver DLL runs anyway with
cells protected. Sounds like there is a logic problem with the way Solver
is set up.

The only think I can recommend is to load you model, and perhaps move just
the Solver portion (The 'unprotected data) to a unprotected sheet.

I remember there is another problem with using the Load command. For the
life of me, I can't remember what is now!! If I find it in my old notes,
I'll pass it along.

 

[Tushar Mehta]

Any other suggestions? Decide what you want to do. Protect the
worksheet against changes? Or make changes to it?

How you expect to both not make changes and make changes is an absolute
mystery!

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office