marking IT

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.

Progressbar example

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:

Instructions: (also see original post):

How to use the Progress Bar in your Spreadsheet applications?

  1. Download the workbook from here.
  2. 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.
  3. 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
  4. 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

  5. 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

  6. After setting the Total number of actions, you can start animating the progress bar using one of the following two methods:
    1. Use the Next Action Method with a status message you would like to show.

      MyProgressbar.NextAction "Performing Action"

    2. Or, manually override the Action Number and the Status message.

      With MyProgressbar.ActionNumber = 5
      .StatusMessage ="Override Test"
      End With
  7. Finally, once the code has finished running, you have three choices:
    1. 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

    2. Execute the Complete Method with the number of seconds you would like to wait before closing the window automatically.

      MyProgressbar.Complete 5

    3. Or, execute the Terminate method and close the Form programmatically. This method you can use on the subordinate progress bars.

      MyProgressbar.Terminate

All credits go to: https://strugglingtoexcel.com

Privacy Settings
We use cookies to enhance your experience while using our website. If you are using our Services via a browser you can restrict, block or remove cookies through your web browser settings. We also use content and scripts from third parties that may use tracking technologies. You can selectively provide your consent below to allow such third party embeds. For complete information about the cookies we use, data we collect and how we process them, please check our Privacy Policy
Youtube
Consent to display content from Youtube
Vimeo
Consent to display content from Vimeo
Google Maps
Consent to display content from Google
Spotify
Consent to display content from Spotify
Sound Cloud
Consent to display content from Sound

marking IT

Contact info