Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turning off all screen updates during a Solver routine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turning off all screen updates during a Solver routine
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turning off all screen updates during a Solver routine
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 +++++++++++++++++++++++++++++++++++ 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turning off all screen updates during a Solver routine
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 * "Tony Scullion" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAE macro for green screen updates with Excel data | Excel Discussion (Misc queries) | |||
Writing a macro that updates solver solutions? | Excel Worksheet Functions | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Solver VBA routine | Excel Discussion (Misc queries) | |||
macros and screen updates | Excel Programming |