Create Progress Bar in Your Report - Part 2

In this post I will show you how to create an actual "bar" to measure the progress of Macro running. You can see an example in the last post (Prog. Bar 2).

To add such a bar to our userform, first make sure the Toolbox is visible (if not, use View -> Toolbox). For those of you who have never used the Microsoft ProgressBar Control before, you need to add that control to your Toolbox. To do this, right-click anywhere in the Toolbox, then choose "Additional Controls". Select "Microsoft ProgressBar Control 6.0", and click "OK". A new control will appear in your Toolbox, which can be dragged to your userform.

Once you have done that and resized/repositioned your progress bar, you can use the code below to set its values. You can download the example file in the last post.

As shown above, it is very easy to control the progress bar - by setting the value of the bar at a certain number between 0 and 100; and the control will automatically set the bar progress at that level.

Bar2.mkdBar.Value = percentage

Once again, the label (Bar2.Label1) will also be refreshed with % numbers, but only at every 5% interval. DoEvents allows the rest of the Macro to be executed normally. Finally, when the loop is finished, we hide the bar with Bar2.Hide.

---

Finally, what if we want to avoid using the Progress Bar control (since it is possible this control may not exist on end-user's computer), and use only those controls that come as default in the Toolbox? There is one walk-around, namely to use a label control with dark background whose width increases over time.

You can write something like this, with Label1 positioned to the right of Label2:

Bar2.Label2.Width = (Bar2.Label1.Left - Bar2.Label2.Left) * percentage / 100