Search code examples
excelvba

How can I create a progress bar in Excel VBA?

I'm doing an Excel app that needs a lot data updating from a database, so it takes time. I want to make a progress bar in a userform and it pops up when the data is updating. The bar I want is just a little blue bar moves right and left and repeats till the update is done, no percentage needed.

I know I should use the progressbar control, but I tried for sometime, but can't make it.

My problem is with the progressbar control, I can't see the bar 'progress'. It just completes when the form pops up. I use a loop and DoEvent but that isn't working. Plus, I want the process to run repeatedly, not just one time.

Solution

  • In the past, with VBA projects, I've used a label control with the background colored and adjust the size based on the progress. Some examples with similar approaches can be found in the following links:

    1. http://oreilly.com/pub/h/2607
    2. http://www.ehow.com/how_7764247_create-progress-bar-vba.html
    3. http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

    Here is one that uses Excel's Autoshapes:

    http://www.andypope.info/vba/pmeter.htm