Forum Discussion

David Griggs's avatar
David Griggs
Copper Contributor
Aug 15, 2018

Application.ScreenUpdating

I have seen the other posts on this topic, where the property does not function in Excel 2016, but there still does not appear to be a remedy for this problem.

 

I, like probably thousands of vba writers are now finding their Excel 2010 (or earlier) macros employing this property to suppress screen flicker (and jumping between workbook windows) now make their applications look very amateurish.

 

With so many questions and comments since the release of Excel 2013, when this issue first occurred, there has not been an explanation from Microsoft as to why this property has been disabled and more importantly whether you accept with the level of concern a fix is required.

 

Please can you explain and how users can overcome this issue.

Macros and VBA

9 Replies

Sort By
  • Elle100's avatar
    Elle100
    Copper Contributor
    Jun 23, 2019

    Try this fix

    Set the ScreenUpdating property on the Workbook object like this:

    Workbooks("your workbook name.xls").Application.ScreenUpdating = False

     ' add your code in here 

    Workbooks("your workbook name.xls").Application.ScreenUpdating = True

    where "your workbook name" is the workbook that you are updating.

    I posted this fix on the excel suggestion forum too.

    • Elle100's avatar
      Elle100
      Copper Contributor
      Jun 23, 2019
      Sorry, I realized I had the workbook (to be updated) minimized and that's why it worked! Try this, although there might be better code to minimize a particular workbook window, the following was OK for me. Application.ScreenUpdating = False Dim wbName As Window Set wbName = Windows("your workbook name") wbName.Visible = False ' other code here Application.ScreenUpdating = True wbName.Visible = True
      • Elle100's avatar
        Elle100
        Copper Contributor
        Jun 23, 2019

        Elle100 

        That didn't format very well, let me try again.  Also, I found a neater way to minimize the workbook being updated.

        Application.ScreenUpdating = False
        Dim wbName As Window
        Set wbName = Windows("name of workbook being updated")
        wbName.WindowState = xlMinimized

        ' other code in here

        Application.ScreenUpdating = True
        wbName.WindowState = xlMaximized

         

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 15, 2018

    Hi David,

     

    Can you explain explicitly what the issue is?

    I use Application.ScreenUpdating frequently in my VBA solutions, but I didn't notice any issue in it.

    • David Griggs's avatar
      David Griggs
      Copper Contributor
      Aug 15, 2018

      This only occurs in Excel 2013 and Excel 2016.

      Typical use for Application.ScreenUpdating would be to supress screen flicker during opening and transfer of data to another workbook. Without this property, the second workbook becomes the active element and the primary window. Even providing the instruction to 'Activate' the first workbook immediately after opening the second still transfers the primary window to the second momentarily.

       

      This becomes even more evident if cell data is moved from one workbook to another.

       

      Using Application.ScreenUpdating in Excel 2010 does exactly as intended and locks the screen while processes such as those described run.   

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Aug 16, 2018

        The reason for that might be the SDI (Single Document Interface) that was first introduced in Excel 2013.

        Please read this https://msdn.microsoft.com/en-us/vba/excel-vba/articles/programming-for-the-single-document-interface-in-excel?f=255&MSPPError=-2147217396 for more information about it.

Resources