Turning off all screen updates during a Solver routine

[Tony Scullion]

Hi Group,

I have a form button on a worksheet(The Best Team), which
when clicked, runs VBA code (provided below) to run a
Solver routine.

Is there any way that I can turn off all the screen
updates so that when I click the button all I get its the
returned results of the Solver routine without viewing it
all happen on screen?

Many thanks

Tony

'Code

Sub FindtheBestTeam()

Application.ScreenUpdating = False

Sheets("Data").Visible = True 'Unhide Data worksheet
Sheets("Data").Select
Range("K6").Select

'Start of Solver Routine
SolverOk SetCell:="$O$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="$K$6:$K$203"
SolverSolve UserFinish:=True 'Do not show any dialog
boxes
' End of Solver Routine

Range("K5").Select
Selection.AutoFilter
Range("K5").Select
Selection.AutoFilter Field:=6, Criteria1:="1"

'Copy the filtered results using excellent generic code
'provided by Tom Oglivy
Call CopyFilter

'Switch off autofilter
Sheets("Data").Select
Range("K5").Select
Selection.AutoFilter Field:=1
Range("K5").Select
Selection.AutoFilter
Range("F6").Select
Sheets("The Best Team").Select
Range("F18").Select

'Hide the Data sheet
Sheets("Data").Visible = False
'View the Best Team
Sheets("The Best Team").Select
Application.ScreenUpdating = True
End Sub

 

[Sean]

Yep. Try this...

Application.Cursor = xlWait
Application.DisplayAlerts = False
Application.ScreenUpdating = False
...............CODE HERE
Application.Cursor = xlDefault
Application.DisplayAlerts = True
Application.ScreenUpdating = True


Let us know.
Sean

 

[Mike Middleton]

Tony Scullion -

I haven't tested this, but it might help to treat SolverSolve as a function
(it returns an integer where 0,1,2 indicate a successful solution). For
example,

If SolverSolve(UserFinish:=True) > 2 Then
MsgBox "Oops!"
End
Else
MsgBox "Whoopee!"
End If

- Mike Middleton, www.usfca.edu/~middleton

+++++++++++++++++++++++++++++++++++

 

[Rob Bovey]

Hi Tony,

Unfortunately, Solver often changes the value of ScreenUpdating back to
True while it's running. It's been a while since I've automated it, so I
don't remember all the specific circumstances in which this happens. At any
rate, the best you can do is keep setting Application.ScreenUpdating = False
after every call to a Solver routine.

--
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 *