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