I lightly tested your idea Tom, I think it may work out. At least I got a
simple manifestation to work.

As for your (rhetorical) question about single-threaded ... I guess, sheer
fantasizing on my part.

Thanks!
Bill;
"Tom Ogilvy" wrote in message
...
Excel isn't multithreaded. What procedure would be running at the same
time
as solver to perform these exotic checks?

Also, your explanation that the target cells has to be on the activesheet
doesn't appear to negate my suggestion of placing your formulas on a
hidden
sheet. Solver manipulates constants. The formulas that refer to those
constants can be on a hidden sheet. The results they produce can be on a
hidden sheet, and then your target cell can refer to that result cell on
the
hidden sheet. Likewise, any constraint cells could be handled the same
way.
If solver truly can't work that way; if it truly will not support formulas
that reference other sheets, then that would be a limitation, but I would
be
surprised. To summarize, everything that solver needs to see is on the
active sheet or the activesheet contains a cell with a link to that
value/result on the hidden sheet. Your confidential formulas are on a
hidden sheet.

--
Regards,
Tom Ogilvy



"William Benson" wrote in message
...
Bernie, I know that you cannot read the formulas while solver is open.
The
problem is that when solver is closed, the sheet is unprotected. There is
no
way I can think of to have Excel know that "Uh Oh, Solver was closed,
guess
I better hurry and re-protect the worksheet." The soonest I can get this
to
happen with my limited skills is the Worksheet_SelectionChange() event,
and
that is not soon enough. The user can left-click on a cell whose formula
should be hidden (note: even if I hide the formulabar, they can just
unhide
it before they navigate) and see the formula just prior to mouse-up. By
running through this sequence over and over, they can ultimately check
each
cell's formula, and even unhide every hidden column.

Are you saying there are no API functions and timers which can check the
state of Excel / vis a vis the Solver?


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
William,

What is the ultimate goal? Protecting the formulas from being
read/stolen?
Because I'm not able to read the formulas in cells while the Solver
dialog
is active....

HTH,
Bernie
MS Excel MVP


"William Benson" wrote in
message
...
Bernie, I appreciate that you have weighed in here. My dilemma is
this:
I
am building an Excel application that my client was very clear that he
wants it to be a self-contained Excel workbook (no DLLs), he wants it
protected within reason (Excel protection is good enough); and he
wants
it to work in Excel 2000 and 2003; and lastly, he wants Solver to be
functional and controllable by the user. He does not want to immitate
the
Solver (i.e., have the program manipulate Solver's variables and
behavior). Adding the Solver reference using VBA code is trivial
(shown
below) but the location of Solver is unreliable, and packaging the
application with a reference already added to the VBAProject is just
asking for a runtime error.
I use a button click to re-enable Solver on the Tools menu, use
SendKeys
to launch it -- but then the user is on their own. A very clever user
will be able to systematically plunder hidden formulas, etc. by taking
advantage of the fact that until Worksheet_SelectionChance event
occurs
(which is where I reapply worksheet protection) they can left-click a
cell, and observe the formula in the formula bar, until mouseup. So I
really do need a way to tell if Solver has been closed, maybe some
routine which can count child windows in Excel, something in API??

Sub GetSolverRef()
Dim Ref As Object, bItemFound As Boolean
For Each Ref In ThisWorkbook.VBProject.References
If UCase(Ref.Name) = "SOLVER" Then
bItemFound = True : Exit For
End If
Next
ThisWorkbook.VBProject.References.AddFromFile _
Application.LibraryPath & "\solver\solver.xla"
End Sub