Excel - Screen flicker on screen update

Question

I've got yet another question from the long serries of 'Stop excel screen from flickering', but I think this one is a bit more tricky.

I've got a client who requires a 'progress' bar on his spreadsheet during macro execution. What I've done, is I've a pie chart linked to 2 cells which I adjust based on the progress. For performance, I've disabled events, ScreenUpdating and changed calculation to manual.

Then, once in a while I quickly toggle ScreenUpdating on and off to actually update the progress chart. This works like a charm every time I use it (more than 30 projects now).

Recently, the client came back to me with a 'big' problem. Whenever I do the quick toggle to refresh the progress chart, the entire excel screen (including all the items on it)flickers, which drives the guy crazy.

Has anyone got any idea on what could be done with this to keep the chart updating while keeping the front page constantly displayed (the macro jumps between pages) without screen flickering? Something like double buffering or so...

SOLUTION

Believe it or not, but I just found a solution. This was so so so easy. In VBA, it's possible to refresh just a specific object, while the remaining screen remains 'locked'. For example:

ThisWorkbook.Worksheets("Welcome").ChartObjects(1).Chart.Refresh

Thank you everyone for your input.

Possibly a userform with a progress bar? (or something like a progress bar) so that you do not need to toggle screenupdating? — NickSlash
Ideally, I'd like to avoid using forms to achieve this. They are sooo ugly, but I'll keep them as a last resort... — ZorleQ

David Zemens David Zemens · Accepted Answer · 2013-03-25T13:43:38

You may be able to rewrite the macro so that it doesn't "jump between pages" (since it's usually not necessary to activate worksheets in order to manipulate their data. Otherwise, either a user form or the application's StatusBar could be used to display a progress bar:

Sub StatusBar()
Dim pct As Double
Dim msg as String
Application.ScreenUpdating = False
For i = 10000 To 1 Step -1
    pct = (10000 - i) / 10000
    msg = Format(pct, "0.0%")  & " complete"
    Application.StatusBar = msg

Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Here is a revised example with UserForm. Note that you will need to add the UserForm module, and at least one Label to it in order to present the information:

Sub StatusBar()
Dim pct As Variant
DoEvents
UserForm1.Show vbModeless

Application.ScreenUpdating = False
For i = 10000 To 1 Step -1
    pct = (10000 - i) / 10000
    If Int((i / 10000) * 100) = (i / 10000) * 100 Then
        msg = Format(pct, "0%")
        UserForm1.Label1 = msg & " complete"
    End If
Next
UserForm1.Label1 = "Finished!"
Application.ScreenUpdating = True
End Sub

Problem might be that, depending on how quickly your macro(s) execute, the progress indicator may appear to jump from 0 to 100%. You can do graphical progress bars, too, but the basic idea behind it would be the same, just instead of updating a label value, you'd be changing the size of a textbox with a differing background color, etc.

Revised per OP comments

This is how you can "copy" a range from one sheet to another sheet without "copy/paste":

Set rng1 = Sheets(1).Rows(12)
Set rng2 = Sheets(2).Rows(1)

rng2.Value = rng1.Value

So, you can just define your ranges, and assign the second range to = the first range.

Excel - Screen flicker on screen update

1 Answers