Screen Updating Problem


S

Shatin

I've been scratching my head how to solve this problem.

Say I have a MacroA which is something like this:

Sub MacroA()
Application.ScreenUpdating = False
Code
Application.ScreenUpdating = True
End Sub

There'll not be any screen flicker when MaroA is run. Everything is sweet.
Now there's a second macro which will run MacroA on all the worksheets:

Sub MacroA_AllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Call MacroA
Next ws
End Sub

For this second macro, however I put in the Application.ScreenUpating
statements, the screen will still display the macro cycling through all the
worksheets. For example, the following does NOT solve the problem:

Sub MacroA_AllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
Application.ScreenUpdating = False
ws.Activate
Call MacroA
Application.ScreenUpdating = False
Next ws
Application.ScreenUpdating = True
End Sub

The only way I can solve the problem is by deleting
Application.ScreenUpdating = True in MacroA, i.e.

Sub MacroA()
Application.ScreenUpdating = False
Code
End Sub

However I am not sure if this is what I want to do because I am not sure if
that means I'll be turning off ScreenUpating for good.

Any advice will be much appreciated.
 
Ad

Advertisements

P

Peter T

Sub MacroA_AllSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets

ws.Activate
Call MacroA

Next ws
Application.ScreenUpdating = True
End Sub

How to handle ScreenUpdating in MacroA depends on how else it may be called
in your project, lets say you have the above and somewhere else simply this

Sub MacrA_ActiveSheet
'best to disable screenupdating here
' but for some reason don't want to
Call MacroA
End Sub


Sub MacroA()
Dim bScrUpdating As Boolean
bScrUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
Code
If bScrUpdating then
Application.ScreenUpdating = True
End If
End Sub

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top