Geek asks and answers

Excel screen False screen display and still flickering screen

I have the following simple code to close a series of open books. I just switched to Excel 2013, and in this new version, my screen continues to blink a white window in Excel for each open book.

How can I make this annoying flicker of the screen turn 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
+3
source share
6 answers

After reading most of the answers with potential solutions, I am sorry to tell you that none of them worked for me, trying to stop the flicker when the worksheet was visible / invisible.

Then it occurred to me that the flicker could be caused by an automatic recount of the book, so I gave it a try, AND THIS WORK!

This is what I use:

(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

Sub

:

StopFlickering ThisWorkbook.Worksheets( " " )

, , , . , .

(PS: , . - - . .)

+2

, .

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

Sub myrunningsub()
    'your code here
End Sub

, Application.ScreenUpdating. . Excel 2013 64- Windows 7.

+1

/ , Application.ScreenUpdating. .

+1

( )... , - .

:
1. StatusBar, - , , !
2. On Error Resume Next , . , ? , .... , , , .

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
0

i "", ( , .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
0

Use WindowStatein conjunction with DisplayAlerts. The user will not see the minimization of the window, but will also prevent Excel from flickering during SaveAs, changing the visibility of the window or changing the protection of the workbook / worksheet.

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
0
source

More articles:


All Articles