CODEWITHSUNDEEP
excelvba
BennoDual
BennoDual

Reputation: 6279

Application.ScreenUpdating = true does not update screen

I have an Excelsheet where I do some dataloading. While this, I turn of Screen Updating with:

Application.ScreenUpdating = False

After I turn it on with:

Application.ScreenUpdating = True

But the problem is, that the screen is still be freezed. This is only occuring on some customer-machines and sudden in the last few days.

When I remove the two ScreenUpdating-Lines, all is working ok.

Does someone know this phenomenon and knows a solution?

Upvotes: 0

Views: 6474

Answers (6)

maxdmvp
maxdmvp

Reputation: 25

I tested a simplified version of option (2) from @Paul Fraser's fix, which also worked. The following code needs to be executed before exiting the function that uses Application.ScreenUpdating:

ActiveWindow.FreezePanes = True
ActiveWindow.FreezePanes = False

Alternatively, this also worked but the screen flashes for a second:

ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

It seems that forcing Excel to perform minor UI interactions "fixes" the issue.

Upvotes: 0

Paul Fraser
Paul Fraser

Reputation: 11

I had similar issues and seems like it may be resolved with one of the following ways: (1) Have VBA switch to a different tab and then back to the tab you want right before ending the code such as using:
(1a)

Sheets("sheet2").Select

Sheets("sheet1").Select

(2) Turn freeze panes off and back on in VBA right before ending the code (2a)

ActiveWindow.FreezePanes = False

ActiveWindow.ScrollRow = 1

ActiveWindow.ScrollColumn = 1

Range("A10").Select

ActiveWindow.FreezePanes = True

(3) Manually turn off Frozen Panes in Excel or
(4) Don't use Application.ScreenUpdating = False but the code runs slower so options 1 or 2 may be the best
(5) In VBA, right before ending the code, using (5a)

ActiveWindow.SmallScroll Down:=10

ActiveWindow.SmallScroll Up:=10

... This had worked for a while but eventually didn't work some of the time

Option 1 or 2 should be the best to avoid a screen that doesn't update after running VBA code, and options 1 and 2 work even when freezing panes in Excel and when using Application.ScreenUpdating = False

Upvotes: 1

Lakafior
Lakafior

Reputation: 11

I've noticed that the problem occurs on 32bit excels with compilation number "2206 16.0.15330.20144". At least people with this problem have this Excel version.

On my 64bit and "2206 16.0.15330.20216" version, everything is running smoothly.

A potential fix would be to force update Excel, or change to the 64bit version.

Upvotes: 1

Conrad Natac
Conrad Natac

Reputation: 1

I have the same problem earlier. I re-installed Microsoft Office. Then, rebooted and it works. I just downloaded the MS office from their portal. Have it re-installed again.

My Excel version is 2305 BNuild 16501.20196.

Upvotes: -1

ExpertNovice
ExpertNovice

Reputation: 61

Maybe the title should be "unable to re-enable ScreenUpdating"

Good idea to pin point version info however, two of my computers are running the following version but only one has the issue.

  • 32-bit version.
  • Product Version 206 16.0.15330.20230

As suggested this reply is where other threads are being cited in order to support our statements. Apparently MS thinks it is a coding issue as the thread found on a Microsoft.com domain says to come to Stack Overflow.

From Stack Overflow's viewpoint, I think the solution is to comment out all "Application.ScreenUpdating = True" statements until Microsoft provides a fix.

The first thread confirms the testing done as mentioned in my previous post.

Jul 11 2022 01:54 PM https://techcommunity.microsoft.com/t5/excel/screen-updating-set-to-true-but-screen-does-not-update/m-p/3570457

This one is totally unhelpful:
Posted late July 11 https://www.qandeelacademy.com/questions/application-screenupdating-true-does-not-update-screen

In this thread Microsoft says to come to Stack Overflow for help. Opened July 3, 2022 https://answers.microsoft.com/en-us/msoffice/forum/all/applicationscreenupdating-true-doesnt-work/8df869d5-df4d-4a08-9abf-674bc4768191

Upvotes: 1

ExpertNovice
ExpertNovice

Reputation: 61

BennoDual,

What I have tried was restarting the computer. The simplest fix, so far, is to comment out all "Application.ScreenUpdating = False" statements. Leaving the "= True" statements alone.

I'm glad it is not "just me" and, since this now a known issue, my solution is to wait, and test each day for the fix. For me this issue arose around 3:30 PM on Sunday July 10. It was working the night before and I think, even Sunday morning.

Here was my testing, after determining the issue.

After creating a new Excel workbook, cell C3 was selected, the following code was executed, and then Sheet1 was reviewed.

Double Clicked A1 and entered "aaa". Result: A1 appeared empty. Name Box showed cell A1 selected. Formula Bar showed "aaa". C3 as still outlined.

Scrolled down quite a ways and Double clicked what appeared to be A1 and entered "bbb". Result: The cell appeared empty. Name Box showed A137. Formula Bar showed "bbb".

I could then use Ctrl+UpArrow and Ctrl+DownArrow which jumped between A1 and A137. Both still appeared empty but the Formula Bar proved otherwise.

To effectively simulate "Application.ScreenUpdating = True" requires shutting down all currently open Excel Workbooks. Not just the one where the "= False" was executed.

Sub Testing()
   Dim booValue As Boolean

   booValue = Application.ScreenUpdating
   Debug.Print booValue    'Result is True

   Application.ScreenUpdating = False
   booValue = Application.ScreenUpdating
   Debug.Print booValue    'Result is False

   Application.ScreenUpdating = True
   booValue = Application.ScreenUpdating
   Debug.Print booValue    'Result is True
End Sub

Upvotes: 0

Related Questions