When your VBA is running for a long time on the background, you want to inform the user about the progress and that it is still active. Excel offers a small progress option in the bottom of the window, but this has minimum visibility.
The above example shows a nice way to solve this. It shows a large and clear progress bar with status text. Also it offers a sub progress layer for iterating child processes.
Instructions on how to implement this in your Excel document, can be found here:
If you want to download an example, please use these links:
- Progress bar VBA (hosted on personal Dropbox)
- Class Progressbar v2.01 (hosted by original author)
- Class Progressbar v2.02 (hosted by original author)
Instructions: (also see original post):
How to use the Progress Bar in your Spreadsheet applications?
- Download the workbook from here.
- 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 ObjectDimMyProgressbar AsProgressBar'Initialize a New Instance of the ProgressbarsSetMyProgressbar = NewProgressBar - Give it a title. Choose if you’d like to change the Excel Status bar also. Set the Start and End Colours.
WithMyProgressbar
'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 = rgbGreenEndWith - 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.
WithMyProgressbar.ActionNumber = 5
.StatusMessage ="Override Test"EndWith
- Use the Next Action Method with a status message you would like to show.
- 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
- 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.
All credits go to: https://strugglingtoexcel.com