View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Thomlinson[_4_] Jim Thomlinson[_4_] is offline
external usenet poster
 
Posts: 1,119
Default Application.ScreenUpdating MsgBox

Once turned off the screen will not update until you turn it back on. A
common mistake is to turn it off at the beginning of every sub and back on at
the end of each sub. This will cause the screen to flash when one sub calls
another. So in your case your sub should look something like this.

Sub Optimise()
on error goto Errorhandler
if MsgBox("ScreenUpdating?", vbYesNo) = vbNo then _
Application.ScreenUpdating = False

If (condition) Then
MySub1
ElseIf (condition) Then
MySub2
End If

ErrorHandler:
Application.ScreenUpdating = True
End Sub

Sub1 and Sub 2 should not do anything with the screen updating. The calling
procedure will take care of that. I have added an error handler which is a
good idea anytime you toggle application settings...
--
HTH...

Jim Thomlinson


"MichaelC" wrote:

I have a master Sub which calls in Sub routines in its body.
I want to be able to switch Screen.Updating on or off for ALL the main sub
and those subs referred to in it, but only answer the MsgBox once at the
beginning of the Sub.
How can I get the Answer to apply to all Subs?
Thanks in advance for your help.
Here is my code:
Sub Optimise()
Ans = MsgBox("ScreenUpdating?", vbYesNo)
Select Case Ans
Case vbYes
Application.ScreenUpdating = True
Case vbNo = False
Application.ScreenUpdating = False
End Select
If (condition) Then
MySub1
ElseIf (condition) Then
MySub2
End If
End Sub