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