Force a screen update in Excel VBA

Question

My Excel tool performs a long task, and I'm trying to be kind to the user by providing a progress report in the status bar, or in some cell in the sheet, as shown below. But the screen doesn't refresh, or stops refreshing at some point (e.g. 33%). The task eventually completes but the progress bar is useless.

What can I do to force a screen update?

For i=1 to imax ' imax is usually 30 or so
    fractionDone=cdbl(i)/cdbl(imax)
    Application.StatusBar = Format(fractionDone, "0%") & "done..."
    ' or, alternatively:
    ' statusRange.value = Format(fractionDone, "0%") & "done..."

    ' Some code.......

Next i

I'm using Excel 2003.

Lapidary

Answer 1

Add a DoEvents function inside the loop, see below.

You may also want to ensure that the Status bar is visible to the user and reset it when your code completes.

Sub ProgressMeter()

Dim booStatusBarState As Boolean
Dim iMax As Integer
Dim i As Integer

iMax = 10000

    Application.ScreenUpdating = False
''//Turn off screen updating

    booStatusBarState = Application.DisplayStatusBar
''//Get the statusbar display setting

    Application.DisplayStatusBar = True
''//Make sure that the statusbar is visible

    For i = 1 To iMax ''// imax is usually 30 or so
        fractionDone = CDbl(i) / CDbl(iMax)
        Application.StatusBar = Format(fractionDone, "0%") & " done..."
        ''// or, alternatively:
        ''// statusRange.value = Format(fractionDone, "0%") & " done..."
        ''// Some code.......

        DoEvents
        ''//Yield Control

    Next i

    Application.DisplayStatusBar = booStatusBarState
''//Reset Status bar display setting

    Application.StatusBar = False
''//Return control of the Status bar to Excel

    Application.ScreenUpdating = True
''//Turn on screen updating

End Sub

Mars

that's a lot of doevents if you do it on each loop, instead do it each 10 or so : if i mod 10 = 0 then Doevents – Oren

I had to use DoEvents followed by a ActiveWindow.SmallScroll up:=1 for a similar issue – Nakasuji

Answer 2

Text boxes in worksheets are sometimes not updated when their text or formatting is changed, and even the DoEvent command does not help.

As there is no command in Excel to refresh a worksheet in the way a user form can be refreshed, it is necessary to use a trick to force Excel to update the screen.

The following commands seem to do the trick:

- ActiveSheet.Calculate    
- ActiveWindow.SmallScroll    
- Application.WindowState = Application.WindowState

Kaylenekayley

Had that problem with a form today. The form and sheet stopped updating. adding the application.windowstate trick every few iterations solved my problem. – Saleable

was working for me with using ActiveWindow.SmallScroll down:=1 ActiveWindow.SmallScroll up:=1 – Ruzich

Answer 3

Put a call to DoEvents in the loop.

This will affect performance, so you might want to only call it on each, say, 10th iteration.
However, if you only have 30, that's hardly an issue.

Contradiction

Answer 4

@Hubisans comment worked best for me.

ActiveWindow.SmallScroll down:=1
ActiveWindow.SmallScroll up:=1

Anther

I had to use DoEvents followed by a ActiveWindow.SmallScroll up:=1 for a similar issue – Nakasuji

In the Microsoft Office 365 (version 2401 Build.16.0.17231.20290) 32-bit version, of all the solutions presented so far (I have previsouly tested them all), this was the only one that made it possible to force the screen of an active sheet to refresh which, after some automation, was remaining blank until the mouse was clicked to make its contents fully visible. – Skulk

Answer 5

This is not directly answering your question at all, but simply providing an alternative. I've found in the many long Excel calculations most of the time waiting is having Excel update values on the screen. If this is the case, you could insert the following code at the front of your sub:

Application.ScreenUpdating = False
Application.EnableEvents = False

and put this as the end

Application.ScreenUpdating = True
Application.EnableEvents = True

I've found that this often speeds up whatever code I'm working with so much that having to alert the user to the progress is unnecessary. It's just an idea for you to try, and its effectiveness is pretty dependent on your sheet and calculations.

Knotts

Answer 6

Specifically, if you are dealing with a UserForm, then you might try the Repaint method. You might encounter an issue with DoEvents if you are using event triggers in your form. For instance, any keys pressed while a function is running will be sent by DoEvents The keyboard input will be processed before the screen is updated, so if you are changing cells on a spreadsheet by holding down one of the arrow keys on the keyboard, then the cell change event will keep firing before the main function finishes.

A UserForm will not be refreshed in some cases, because DoEvents will fire the events; however, Repaint will update the UserForm and the user will see the changes on the screen even when another event immediately follows the previous event.

In the UserForm code it is as simple as:

Me.Repaint

Eupepsia

Answer 7

This worked for me:

ActiveWindow.SmallScroll down:=0

or more simply:

ActiveWindow.SmallScroll 0

Allege

Answer 8

I couldn't gain yet the survey of an inherited extensive code. And exact this problem bugged me for months. Many approches with DoEnvents were not helpful. Above answer helped. Placeing this Sub in meaningful positions in the code worked even in combination with progress bar

Sub ForceScreenUpdate()
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Wait Now + #12:00:01 AM#
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Sub

Farinose

this sub ends with properties on false. That doesn't make sense. – Manchukuo

Answer 9

In my case: A complex Excel with a graphic simulation using shapes which move. In order to speed up, I wished to update the display only each 10th optimization step. Therefore, every 10th step, I had to switch it back on, followed by a doevents - but I ran into the problems described above. - Now, which is odd: Simply stating the screenupdating=True TWICE, it works. ??!! This is crazy.

        If counteR Mod 10 = 0 Then
            'must state this twice. WHY ??
            Application.ScreenUpdating = True
            Application.ScreenUpdating = True
            DoEvents
            Application.ScreenUpdating = False
        End If

Chong

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Constitute

Answer 10

On a UserForm two things worked for me:

1. I wanted a scrollbar in my form on the left. To do that, I first had to add an Arabic language to "Change administrative language" in the Language settings of Windows 10 (Settings->Time & Language->Change Administrative Language). The setting is actually for "Change the language of Non-Unicode Programs," which I changed to Arabic (Algerian). Then in the properties of the form I set the "Right to Left" property to True. From there the form still drew a partial ghost right scrollbar at first, so I also had to add an unusual timed message box:

    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    'Set the message box to close after 10 seconds
    AckTime = 1
    Select Case InfoBox.Popup("Please wait.", AckTime, "This is your Message Box", 0)
    Case 1, -1
    End Select

2. I tried everything to get the screen to redraw again to show the first text box in it's proper alignment in the form, instead of partially underneath or at least immediately adjacent to the scrollbar instead of 4 pixels to the right where I wanted it. Finally I got this off another Stackoverflow post (which I now can't find or I would credit it) that worked like a charm:

Me.Frame1.Visible = False
Me.Frame1.Visible = True

Psychokinesis

Answer 11

In my case the problem was in trying to make one shape visible and another one invisible on a worksheet.

This is my approach to "inactivating" a button [shape] once the user has clicked it. The two shapes are the same size and in the same place, but the "inactive" version has dimmer colors, which was a good approach, but it didn't work, because I could never get the screen to update after changing .visible = FALSE to = TRUE and vice versa.

None of the relevant tricks in this thread worked. But today I found a solution that worked for me, at this link on Reddit

Essentially you just call DoEvents twice in immediate succession after the code that makes the changes. Now why? I can't say, but it did work.

Peregrinate

Answer 12

I've been trying to solve this Force a screen update on a Worksheet (not a userform) for many years with limited success with doevents and scrolling etc.. This CH Oldie solutions works best with a slight mod.

I took out the Wait and reset ScreenUpdating and EnableEvents back to true.

This works office excel 2002 through to office 365

Sub Sheet1Mess(Mess1 As String)
    Sheet1.Range("A6").Value = Mess1
    ForceScreenUpdate
End Sub
Sub ForceScreenUpdate()
    Application.ScreenUpdating = True
    Application.EnableEvents = True
   ' Application.Wait Now + #12:00:01 AM#
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Mitchell

Answer 13

The fix that works for me (and the only thing that's required) is to make sure screen updating is enabled and add a wait statement:

Application.ScreenUpdating = True
'Wait statement is essential
Application.Wait (Now + TimeValue("0:00:01"))
'Allows rest of app to run much faster
Application.ScreenUpdating = False

As others have said, turning screen updates off (and anything else you can do without, especially automatic calculation) will usually make your VBA run much faster. So there's no problem in leaving screen updating set false; that's usually a very good idea.

Broke

Answer 14

Works ok:

Application.ScreenUpdating = True
ActiveWindow.SmallScroll down:=1
ActiveWindow.SmallScroll up:=1
DoEvents

Henequen

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Pontonier

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Tight