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

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

This worked for me in Excel 2000

Public bol As Boolean

Sub CheckScreenUpdate()

Application.ScreenUpdating = bol

msgbox Application.ScreenUpdating
End Sub

Sub Optimise()
Dim Dim ans as Long
ans = MsgBox("ScreenUpdating?", vbYesNo)

If ans = 6 Then
bol = True
Else
bol = False
End If
'If (Condition) Then
'MySub1
'ElseIf (Condition) Then
'MySub2
'End If
End Sub


--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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

Thank you very much Jim and Steve. Your answers not only help me avoid
temporary insanity chasing my own tail, but also give me learnings for other
problems.

"Jim Thomlinson" wrote:

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