I believe I owe you an explanation; about this post’s title. At first, you might think I am a pompous prick, branding my work myself as First Class. This Progress Bar was my first project after I learned how to set up class modules: hence the name. The progress bar that I developed earlier, is one of my most visited posts, and I thought I have to reward my readers with a progress bar that is much easier to use.

What’s new?

In addition to being more user-friendly, I added in a few touches that you might like:

Since I have set this userform up with Properties and Methods, a separate standard code module is no longer required to animate the bar. I have bundled all the code needed to use the Progress Bar into the userform itself. You can create new instance of class object that allows you to create and control multiple progress bars simultaneously. I also tweaked the lay out a little; made it more compact. Also, you can make the colour of the bar change gradually from start to end. I discarded the “Abort” button, but not completely. A few of my readers appreciated the idea of stopping the code when they wanted. Now, you can click the close button on top of the form to terminate execution.

How to use the Progress Bar in your Spreadsheet applications?

Download the workbook from the Downloads Section below. Import the User Form into your project. You can just drag it from my workbook and drop it in your project, in the Project Explorer Window. Declare and Create a new Instance of the ProgressBar Class. 'Declare the ProgressBar Object Dim MyProgressbar As ProgressBar 'Initialize a New Instance of the Progressbars Set MyProgressbar = New ProgressBar Give it a title. Choose if you’d like to change the Excel Status bar also. Set the Start and End Colours. With MyProgressbar 'Set the Title .Title = "Test The Progress Bar" 'Set this to true if you want to update 'Excel's Status Bar Also .ExcelStatusBar = True 'Set the colour of the bar in the Beginning .StartColour = rgbMediumSeaGreen 'Set the colour of the bar at the end .EndColour = rgbGreen End With Set the Total number of actions you expect to perform first. Remember that you would not be able to change this later on. MyProgressbar.TotalActions = 100 After setting the Total number of actions, you can start animating the progress bar using one of the following two methods: Use the Next Action Method with a status message you would like to show. MyProgressbar.NextAction "Performing Action" Or, manually override the Action Number and the Status message. With MyProgressbar .ActionNumber = 5 .StatusMessage ="Override Test" End With Finally, once the code has finished running, you have three choices: Execute the Complete Method to let the user know that it is over, and let the user close the form when they feel like it. This you can use on the Main Progress Bar. MyProgressbar.Complete Execute the Complete Method with the number of seconds you would like to wait before closing the window automatically. MyProgressbar.Complete 5 Or, execute the Terminate method and close the Form programmatically. This method you can use on the subordinate progress bars. MyProgressbar.Terminate

Feel free to rearrange the Form Controls however you please, but remember to not play around with the names though. I have written a couple of cover macros in workbook to illustrate how to use this progress bar. I also have and example that uses two progress bars simultaneously. Let me know if you get stuck somewhere, I will be happy to help.

Downloads

I received a feedback from readers who use multiple displays that the progress bar is not centered to their Excel window. They even gave me code snippets to resolve the problem; thank you all. Here is a updated version that centers the progress bar to Excel’s Active Window.

Further Reading

I was intimidated by the mere thought of learning how to use Class Modules, but not anymore. Chip Pearson’s article on Classes in VBA was my starting point. I urge you to check it out. And I have added plenty of comments in my code; perhaps that will also help you understand how to use classes in your code.