dcsimg
Results 1 to 14 of 14

Thread: Application.ScreenUpdating not working in Excel 2013

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Application.ScreenUpdating not working in Excel 2013

    I have a macro with the "usual"
    Code:
    Application.ScreenUpdating = True
    that's working normally in Excel 2010
    but when porting it to Excel 2013 it simply doesn't do anything

    do someone know about this being a known issue?

    thanks

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,628

    Re: Application.ScreenUpdating not working in Excel 2013

    normally screen updating is always set to true when the code is finished
    is this not happening?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Re: Application.ScreenUpdating not working in Excel 2013

    Quote Originally Posted by westconn1 View Post
    normally screen updating is always set to true when the code is finished
    is this not happening?
    yes it does

    I didn't put it correctly
    my problem is that
    Code:
    Application.ScreenUpdating
    isn't processed at all

    when my macro sets it to False and then back to True after some processing
    but it doesn't work: after setting it to False I still get screen updating

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Re: Application.ScreenUpdating not working in Excel 2013

    so it seems like there's no solution to this issue

    I must stick to excel 2010

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,560

    Re: Application.ScreenUpdating not working in Excel 2013

    I'm using 2013, and it works fine for me.

    Put this code in a module and run it with a blank worksheet active:

    Code:
    Sub putNumbersInCells()
        Dim ws As Worksheet
        Dim j As Long
        Dim resp As Integer
        
        Set ws = ActiveSheet
        
        resp = MsgBox("Turn off updating?", vbYesNo)
        
        If resp = 6 Then
            Application.ScreenUpdating = False
        Else
            Application.ScreenUpdating = True
        End If
        
        For j = 1 To 200000
            ws.Range("a" & j).Value = j
        Next j
        
        Application.ScreenUpdating = True
        
        MsgBox "done"
        
    End Sub
    When I say "yes" to the "turn off update" message box, it processes the whole For Loop before displaying the results in the cells. When I say "no" it begins displaying them immediately.

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Re: Application.ScreenUpdating not working in Excel 2013

    Quote Originally Posted by vbfbryce View Post
    I'm using 2013, and it works fine for me.

    Put this code in a module and run it with a blank worksheet active:

    Code:
    Sub putNumbersInCells()
        Dim ws As Worksheet
        Dim j As Long
        Dim resp As Integer
        
        Set ws = ActiveSheet
        
        resp = MsgBox("Turn off updating?", vbYesNo)
        
        If resp = 6 Then
            Application.ScreenUpdating = False
        Else
            Application.ScreenUpdating = True
        End If
        
        For j = 1 To 200000
            ws.Range("a" & j).Value = j
        Next j
        
        Application.ScreenUpdating = True
        
        MsgBox "done"
        
    End Sub
    When I say "yes" to the "turn off update" message box, it processes the whole For Loop before displaying the results in the cells. When I say "no" it begins displaying them immediately.
    thank you vbfbryce
    I'll try and check that out as soon as I get to an Excel 2013

    Meanwhile I can add something that could help you help me out
    I get the unwanted flickering occurs while switching between workbooks. So I put breaking points in every line code calling any "activate" method (cell, worksheet, workbook) checking for "?Application.ScreenUpdating" in the immediate window and finding out that it was set to "True" even if I managed to set it to "False" before that "activate" call.
    So I digged into the sub that manages Application.ScreenUpdating setting, which follows

    Code:
    Sub myScreenUpdate(screenUpdateRequest As Boolean, previousScreenUpdate As Boolean)
    
    If screenUpdateRequest Then
        Application.ScreenUpdating = previousScreenUpdate
    Else
        previousScreenUpdate = Application.ScreenUpdating
        Application.ScreenUpdating = False
    End If
    
    End Sub
    which is first called like follows
    Code:
    Call myScreenUpdate(False, previousScreenUpdate)
    and then
    Code:
    Call myScreenUpdate(True, previousScreenUpdate)
    And there I found that the first call (and the second too, I guess) was uneffective

    Can it be it's because of some maliciuos interaction between Application.ScreenUpdating and Application.EnableEvents settings? Since they get set in quite a mixed mode along the code: somtimes the former before the latter and sometimes in reverse.
    But always remind that I get no flickering in Excel 2010.
    Finally I must add that those two Excel run on different machines: Excel 2010 with W7 and Excel 2013 with W8

    I think that's all, for now

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,628

    Re: Application.ScreenUpdating not working in Excel 2013

    I get the unwanted flickering occurs while switching between workbooks.
    you should avoid any code that selects or activates anything, work with fully qualified ranges instead of selection

    i believe that screenupdating can only be relied on to work correctly within the procedure it is called, if in doubt just put at the beginning of every procedure
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,281

    Re: Application.ScreenUpdating not working in Excel 2013

    yes, follow westconns advice and get rid of the .activate and .select stuff.

    i noticed that your myScreenUpdate sub requires the following sequence:
    Code:
    dim previousScreenUpdate as boolean
    Call myScreenUpdate(False, previousScreenUpdate) 'to get the current setting into previousScreenUpdate
    Call myScreenUpdate(True, false)  'to set updating to false
    'do your heavy code here and when finished:
    Call myScreenUpdate(True, previousScreenUpdate)
    maybe you have fiddled around with the code and thought the first "Call myScreenUpdate(True, previousScreenUpdate)" is useless/wrong? i must admit that myScreenUpdate does not help to make the code readable so this mistake could have happened to many of us...
    Last edited by digitalShaman; Mar 27th, 2015 at 07:41 AM.

  9. #9
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,560

    Re: Application.ScreenUpdating not working in Excel 2013

    To follow up on Pete and DS, why are you switching between workbooks? Application.Screenupdating=false won't prevent the "switching" from being displayed, so I'm not understanding why you wouldn't see that in 2010 as well.

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Re: Application.ScreenUpdating not working in Excel 2013

    Quote Originally Posted by vbfbryce View Post
    To follow up on Pete and DS, why are you switching between workbooks? Application.Screenupdating=false won't prevent the "switching" from being displayed, so I'm not understanding why you wouldn't see that in 2010 as well.
    starting from your kind last three posts I checked more thoroughly my code and can state what follows:
    - I do have "activate" stuff but it resides in subs only with the specific task of taking the user around many open workbooks (rather than having him select them through taskmanager, or alt+tab or via the window bottom toolbar)
    while the main sub (and those called by it), which is called for processing data in all open workbooks, does not make use of any "activate" method at all
    - my guess about switching between workbooks being the flickering cause was just a guess that turned out to be wrong.
    - still, I have that flickering with excel 2013-W8 and not with excel 2010-W7

    since the main sub (and those called by it) has to open all those many workbooks, for reading data inside them, I'm starting to think flickering is due to this. but with no idea of why should that happen with excel 2013-W8 only. besides, both machines are laptops and the excel2013-W8 being more powerful, so that should not be a matter of opening file speed, should Application.ScreenUpdating = False not act on file openings.


    and just to finish checking all of your suggestions/doubts
    @westconn1: I actually put calls to myScreenUpdatejust(False, previousScreenUpdate) at the beginning of every procedure to make sure I had 'Application.ScreenUpdating' set to False (and store its previuous value) before going through it. and then I also put calls to myScreenUpdatejust(True, previousScreenUpdate) at the end of every procedure to set it back to its previuous value
    so the issue could be the myScreenUpdatejust() doesn't work properly

    @DS: I always use myScreenUpdate() in the following way
    - first
    Code:
    Call myScreenUpdate(False, previousScreenUpdate)
    to store the current value of Application.ScreenUpdating and then (trying to) set it to "False"

    - then
    Code:
    Call myScreenUpdate(True, previousScreenUpdate)
    to set Application.ScreenUpdating to its previuos value, not necessarily to True. this to mantain possible settings to False coming from calling subs

    Am I missing something? I know that your own errors are the hardest to be caught by yourself

    @vbfbryce: still not getting at the excel 2013-W8 machine to test your code. I guess I'll do by this w.e.

    thanks to all

  11. #11
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Application.ScreenUpdating not working in Excel 2013

    You could try the technique proposed by Excelerator_Marc in this thread (last post in thread).
    Screen Flicker - EXCEL 2013 Only

    Just in case MS does something with there forum posts as they often do, here is his proposed work-a-round.

    BROKEN:
    Code:
    Sub Test()
       application.screenupdating = false
       'your code here'
       application.screenupdating = true
    end sub
    Working:
    Code:
    Sub test()
       application.screenupdating = false
       call yourcodesub()
       application.screenupdating = true
    end sub
    
    sub yourcodesub()
       'your code here'
    end sub

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,628

    Re: Application.ScreenUpdating not working in Excel 2013

    here is his proposed work-a-round.
    i really would have thought the opposite would have more likely been the case
    never stop learning
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Application.ScreenUpdating not working in Excel 2013

    Quote Originally Posted by westconn1 View Post
    i really would have thought the opposite would have more likely been the case
    never stop learning
    The more I read about issues with the releases since Office 2007, I am glad that I have no real reason to upgrade.

    My favorite was about Excel 2010 and outdated color printer drivers where having the the supposedly outdated printer selected as the default printer causes constant screen refreshes, flickering and other issues.

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2014
    Posts
    12

    Re: Application.ScreenUpdating not working in Excel 2013

    Quote Originally Posted by TnTinMN View Post
    You could try the technique proposed by Excelerator_Marc in this thread (last post in thread).
    Screen Flicker - EXCEL 2013 Only

    Just in case MS does something with there forum posts as they often do, here is his proposed work-a-round.
    thank you TnTinMN

    so it turns out it is actually an Excel 2013 issue... well that's both reassuring and frustrating at the same time. but mostly the latter!
    I'll try Excelerator_Marc workaround this w.e. and I feel like it will solve the problem since my code does a lot of sheet protection setting, which is exactly A_CP case, who started that MSDN thread off
    moreover I'll make my current setting storing variable public and make my "myScreenUpdate()" act on it only if it actually differs from the requested one


    thanks again to all of you guys
    I'll let you know about the, hopefully happy, ending

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width