Application.ScreenUpdating = False / does it not alway work ?

L

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Hello,

I always use standard colors in my xl applications.

The macro colorConventions below applies the standards to the activeSheet.
It works correctly without any perturbing screen animation thanks to the statement "Application.ScreenUpdating = False"

The next macro colorConventions2 below does the same for a complete activeWorkbook.
It does the job.
But unfortunately, it comes with perturbing screen animations.
Apparently the statement "xl.ScreenUpdating = False" is not effective.

Any clue ?

Rich (BB code): 
Sub colorConventions()
    On Error Resume Next
    Application.ScreenUpdating = False
    Set currentSel = Selection
    Err.Clear
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers).Select
    If Err = 0 Then Selection.Font.ColorIndex = 3
    Err.Clear
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Select
    If Err = 0 Then Selection.Font.ColorIndex = 5
    Err.Clear
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Select
    If Err = 0 Then Selection.Font.ColorIndex = 50
    If ActiveWindow.GridlineColorIndex = 24 Then
        ActiveWindow.GridlineColorIndex = 0
        ActiveWindow.DisplayGridlines = False
    Else
        ActiveWindow.GridlineColorIndex = 24
        ActiveWindow.DisplayGridlines = True
    End If
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        pt.ColumnRange.Font.ColorIndex = 5
        pt.RowRange.Font.ColorIndex = 5
        pt.DataLabelRange.Font.ColorIndex = 5
        pt.DataBodyRange.Font.ColorIndex = 50
    Next pt
    currentSel.Select
    Application.ScreenUpdating = True
End Sub
Rich (BB code): 
Sub colorConventions2()
    On Error Resume Next
    Set xl = Application
    Set ThisRecipeWorkbook = ActiveWorkbook
    xl.ScreenUpdating = False
    Set curSh = xl.ActiveSheet
        For Each sh In ThisRecipeWorkbook.Worksheets
            If sh.Visible Then
                sh.Select
                Set currentSel = xl.Selection
                Err.Clear
                sh.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers).Select
                If Err = 0 Then xl.Selection.Font.ColorIndex = 3
                Err.Clear
                sh.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Select
                If Err = 0 Then xl.Selection.Font.ColorIndex = 50
                Err.Clear
                sh.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues).Select
                If Err = 0 Then xl.Selection.Font.ColorIndex = 5
                Err.Clear
                sh.Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Select
                If Err = 0 Then xl.Selection.Font.ColorIndex = 5
                ActiveWindow.GridlineColorIndex = 0
                ActiveWindow.DisplayGridlines = False
                For Each pt In sh.PivotTables
                    pt.ColumnRange.Font.ColorIndex = 5
                    pt.RowRange.Font.ColorIndex = 5
                    pt.DataLabelRange.Font.ColorIndex = 5
                    pt.DataBodyRange.Font.ColorIndex = 50
                Next pt
                currentSel.Select
            End If
        Next sh
    curSh.Activate
    xl.ScreenUpdating = True
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
In Excel 2000 I get no screen activity at all with your second procedure, until it is finished. What happens for you?
 
Upvote 0
I think selecting sheets resets it to True. You probably don't need to be doing all that selecting - you should be able to rewrite the code without selects/activates. This would have the additional benefit of speeding up your macro.


EDIT: Nope, on testing it doesn't. The suggestion to do away with selects/activates still applies though
 
Last edited:
Upvote 0
Thanks a lot Andrew and Richard, your comments will be helpful.
I will avoid selecting. I don't remember why I did it in first place.

However, I still have a problem with the gridlines.
It seems these properties apply only to a "Window" object and there is no way to go from a sheet to its window if it does not appear on the screen.

Any suggestion would be welcome too !
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,719
Messages
6,126,432
Members
449,314
Latest member
MrSabo83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top