I am launching solver via a command button which uses SendKeys to invoke the
dialog. I cannot control what the user does with solver and I don't want to.
What I do need, is some way to "know" the very instant that they close the
Solver dialog. The closest I can get is the Worksheet_SelectionChange, and
it is not good enough. Can someone recommend something I can do from API
perhaps? Some condition I can test on a timed basis maybe?

William,

Don't use the dialog. Instead, control how the interaction between your user and solver progresses.
See

http://support.microsoft.com/kb/843304

for details, as well as the examples in VBA help.

HTH,
Bernie
MS Excel MVP


"William Benson" wrote in message
...
I am launching solver via a command button which uses SendKeys to invoke the dialog. I cannot
control what the user does with solver and I don't want to. What I do need, is some way to "know"
the very instant that they close the Solver dialog. The closest I can get is the
Worksheet_SelectionChange, and it is not good enough. Can someone recommend something I can do from
API perhaps? Some condition I can test on a timed basis maybe?

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

Why not put your formulas on a hidden sheet and only reference those cells
on the current sheet.

--
Regards,
Tom Ogilvy



"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

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

I cannot ... the target cell in Solver must be on the active sheet, and the
sheet must be unprotected.

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

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

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