1
votes

I've code that takes 30+ mins to run. I have set screen updating to false while most of the code runs but every now and again I turn it to true and straight back to false.

In 2003, 2007 and 2010 this allows the screen to temporarily update. In 2013 it doesn't work.

How can I make Excel 2013 temporarily update the screen mid-macro?

Sub Test()
    Application.ScreenUpdating = False
    ' Do loads of stuff here
    Application.ScreenUpdating = True ' Enable to refresh screen
    Application.ScreenUpdating = False ' Disable again
    ' Do more stuff here
    Application.ScreenUpdating = True
End Sub
4
Have you tried using DoEvents before updating the screen?yu_ominae
Here is a question which might help with your query by the way. stackoverflow.com/questions/3863641/…yu_ominae

4 Answers

2
votes

DoEvents worked for me. I don't know what causes this but inserting DoEvents in the Loop Method seems to correct the issue for me when I use it.

Sub LoopMethod
    Foreach i in Identity
        Call Loopthis 
    Next For
EndSub

Sub Loopthis 
    DoEvents
    select.Cells(i, 6)
EndSub
1
votes

I do place this code right before every event/screen I want to update and works fine for me:

Application.ScreenUpdating = True
DoEvents

Sheets("Main").Select   'Screen to update

Also I put the code inside every loop I use to write someting in the Sheet.

0
votes

Activating another worksheet and then again activating the required worksheet worked for me - I had a code where the screen updating failed, I activated another worksheet and then again the one I was working on, it updated the current screen.

application.ScreenUpdating = False

''''''code'''''''

Thisworkbook.worksheets(any otherworksheet in workbook).activate
Thisworkbook.worksheets(current worksheet).activate

application.ScreenUpdating = True
0
votes

As far as I have found the issue can be solved by jumping to another cell. You could do something like this:

Dim Ac as object
Set Ac = ActiveCell
Ac.Offset(0,IIf(Ac.Column = Application.Columns.Count, -1, 1)).Activate
Ac.Activate

As to the how or why of this behaviour I don't know (yet). As far as I have found now, it has something to do with the active cell before deactivating screen update, being the same as after reactivating screen update. But I haven