Hi Ken,
The ability to use Solver from another application appears to be a
feature that was cobbled on as an afterthought, with no real consideration
given for how the calling application might be affected. Therefore, it tends
to do things like this. Rather than using a global flag variable in this
case, though, why not use Excel's built-in mechanism for disabling events:
Application.EnableEvents = False
''' Call Solver here.
Application.EnableEvents = True
Just make sure EnableEvents does get set back to True, because it's a
persistent setting, and all Excel events will be disabled while it's False,
even if your code is no longer running.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Ken Dahlberg" wrote in message
om...
I'm going to try posting this again - it was late in the day the first
time.
(Ken Dahlberg) wrote in message
. com...
With Solver in Excel 2002: I have a solver model activated by an
embedded CommandButton on the DeNorm worksheet as
shown in code below. Note the two lines that set a global variable
called BypassEvents. On the DeNorm, FitPlot and CapPlot worksheets,
in their Worksheet_Activate and Worksheet_Deactivate modules, I have
the beginning line
If BypassEvents then Exit Sub
Why do I need this? Because the SolverSolve routine activates and
de-activates each of these worksheets at least once each time it is
called, which creates all kinds of havoc unless the event modules are
bypassed. The FitPlot worksheet has another solver model on it, but
the CapPlot worksheet doesn't. The cells in the DeNorm solver model
do not refer in any way to anything on those other worksheets.
What gives? Why is SolverSolve doing this, and is there a more direct
way to control this behavior?
Thanks,
Ken Dahlberg
Private Sub FitButton_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
BypassEvents = True
SolverReset
Application.ScreenUpdating = False
SolverOK setCell:=Range("Q40"), _
MaxMinVal:=2, _
ByChange:=Range(ParmRange) ' ParmRange is a string defining
Application.ScreenUpdating = False ' Changing Cells for the model.
SolverSolve UserFinish:=False
BypassEvents = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub