Search code examples
excelvbauserform

How to display a progress bar?

There is much advice on the Internet about how to make a progress bar.

I made a userform, with label and frame and added code to the button:

Private Sub CommandButton1_Click()

For i = 1 To 192
    Cells(i, 1) = "a"
    Label1.Width = i
Next i

End Sub

The progress bar is refreshed only after the end of execution of loop.

How to refresh a userform?

Solution

  • DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue (like screen updates). This is what you need if you are using a progress bar.

    So put a DoEvents inside of your loop. It will slow it down, but the progress bar should work then.

    Private Sub CommandButton1_Click()
  For i = 1 To 192
    Cells(i, 1) = "a"
    Label1.Width = i
    DoEvents
  Next i
End Sub

    VBA DoEvents Documentation from Microsoft