Excel ScreenUpdating False and still flickering screen

Question

I have the following simple code to close a range of open workbooks. I have just switched to Excel 2013 and in this new version my screen keeps flashing a white window in Excel for each workbook that is unhidden.

How can I get that annoying screen flicker to shut off?

Sub CloseFiles()
    On Error Resume Next

    Application.ScreenUpdating = False
    Application.StatusBar = "Please wait while files are closed."
    Application.DisplayAlerts = False

    Dim rCell As Range
    For Each rCell In Range("Files")
      Application.StatusBar = "Closing file " & rCell.Value
      If rCell.Value <> "" Then
         Windows(rCell.Value).Visible = True
         Workbooks(rCell.Value).Close SaveChanges:=True
      End If
    Next rCell

    Application.WindowState = xlMaximized
    Windows("Filename.xlsm").Activate

    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

[On Error Resume Next?](http://stackoverflow.com/questions/21673529/if-not-function-proceeds-when-value-is-the-one-specified/21673617#21673617) BTW you don't need to make the window visible to close it. Comment out the line `Windows(rCell.Value).Visible = True` and try again? — Siddharth Rout, Feb 12 '14 at 07:49
@SiddharthRout exactly what I was thinking - why bother with that line of code? The save will be ok even if the workbook is not visible. ....maybe I'll rush an answer on now! — whytheq, Feb 12 '14 at 08:02
Thanks for your comments. I have added the line to make the workbooks visible again, so that when I open them the next time, they don't open hidden. Because I have a lot of files open at the same time, I do hide them when I need them to consolidate all the figures. But when I want to look at them individually, I don't want to have them opened hidden, as it might confuse a user not finding the just opened workbook. — Matth, Feb 12 '14 at 10:13

score 6 · Answer 1 · edited Mar 08 '18 at 21:07

Use WindowState in combination with DisplayAlerts. The user won't see the window minimize, but it will also keep Excel from flickering during a SaveAs, changing window visibility, or changing workbook/worksheet protection.

Dim iWindowState as Integer

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    iWindowState = .WindowState
    .WindowState = xlMinimized
End With

'Flickery code

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .WindowState = iWindowState
End With

score 3 · Answer 2 · edited Jul 31 '19 at 15:58

Having read most of the answers with potential solutions, I'm sorry to tell you none of them worked for me when trying to stop the flickering at the moment of making a worksheet visible/invisible.

Then, it came up to me that the flickering could be caused by the automatic recalculation of the workbook, so I gave it a try, AND IT WORKED!

This is what I'm using:

 Private Sub StopFlickering (ws As Worksheet)

  Application.Calculation = xlManual
  Application.ScreenUpdating = False

  'Make the worksheet visible/invisible according to previous condition
  'Instead, use "= True" or "= False", if it is the specific case
  ThisWorkbook.Worksheets(ws.Name).Visible = _ 
    Not ThisWorkbook.Worksheets(ws.Name).Visible

  '(any other code in here)

  'Don't forget to restore previous settings
  Application.ScreenUpdating = True  
  Application.Calculation = xlAutomatic

End Sub

Example to use it from anywhere in the workbook:

StopFlickering ThisWorkbook.Worksheets("Worksheet Name")

I hope it works not just for me, but for anyone who gives it a try. Good luck, and let me know.

score 3 · Answer 3 · answered Aug 26 '17 at 15:36

3

Protecting/unprotecting a sheet activates the sheet, despite the setting for Application.ScreenUpdating. This was the cause for my flicker.

answered Aug 26 '17 at 15:36

Dean Meyer

31
1

@ Dean Meyer Can the activation of the sheet be avoided? – 71GA Sep 07 '22 at 12:35

score 1 · Answer 4 · edited Dec 05 '15 at 14:24

1

I have determined the easiest way to solve this is to call the code from a separate subroutine.

Sub startcode()
     Application.ScreenUpdating = False
     Call myrunningsub()
     Application.ScreenUpdating = True
End Sub

Sub myrunningsub()
    'your code here
End Sub

This seems to work in binding the Application.ScreenUpdating parameter. Note I use Excel 2013 in Windows 7 64 bit.

edited Dec 05 '15 at 14:24

micstr

5,080
8
48
76

answered Mar 04 '15 at 00:35

Excelerator_Marc

11
1

whytheq · Answer 5 · 2014-02-12T08:11:57.070

As Siddharth mentioned (I was just a second behind) ....why bother making the books visible - just close each and save changes.

Couple of other points:
1. I used to play around with the applications StatusBar but don't bother anymore - too many times where the application crashes mid-procedure and the bar is left with an unwanted message on it!
2. Is On Error Resume Next required in the flow of the program e.g. are you using it to deliberately hit an error and then move on to the next line of code? If not then just hiding an error can be dangerous....sometimes best to let programs error and then you know where you stand and can then fix the problem.

Sub CloseFiles()
    On Error Resume Next

    Application.ScreenUpdating = False
    Application.StatusBar = "Please wait while files are closed."
    Application.DisplayAlerts = False

    Dim rCell As Range
    For Each rCell In Range("Files")
      Application.StatusBar = "Closing file " & rCell.Value
      If rCell.Value <> "" Then
         'Windows(rCell.Value).Visible = True  '::::::::why bother with this?
         Workbooks(rCell.Value).Close SaveChanges:=True 
      End If
    Next rCell

    Application.WindowState = xlMaximized
    Windows("Filename.xlsm").Activate

    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

I added the On Error Resume next cos I want to run the close process through, although a specific file might no longer be open. Without that, it will hit an error and not close the remaining open books. And yes, the status bar is just a nice to have, but it at least provides the user with some kind of progress report, otherwise you don't know if the macro is still working or not. — Matth, Feb 12 '14 at 10:15

score 0 · Answer 6 · answered Feb 13 '14 at 15:33

i Had the same "problem", and i use this kind of code (i add a doevents, and a .displaystatusbar=true)

Sub CloseFiles()
err.clear
On Error Resume Next

with Application
    .ScreenUpdating = False
    .displaystatusbar = true 'kinda need this line
    .StatusBar = "Please wait while files are closed." 
    doevents 'magic trick
    .DisplayAlerts = False
    .calculation= xlManual  'sometimes excel calculates values before saving files
    .enableevents=false  'to avoid opened workbooks section open/save... to trigger
end with

'code


with application
        .StatusBar = False
        .displaystatusbar = false
        .DisplayAlerts = True   
        .ScreenUpdating = True
        .enableevents=true
        .calculation= xlAutomatic
end with

End Sub

Excel ScreenUpdating False and still flickering screen

6 Answers6

Linked