First Class Progress Bar for all your Macros


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 more easier to use.

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

  1. 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.
  2. You can create new instance of class object that allows you to create and control multiple progress bars simultaneously.
  3. 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.
  4. 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.

Progress Bar

How to use the Progress Bar in your Spreadsheet applications:

  1. Download Class Progress Bar V2.01.xlsm from Dropbox.
  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
        .Title = "Test The Progress Bar" 'Set the Title
        .ExcelStatusBar = True 'Set this to true if you want to update Excel's Status Bar Also
        .StartColour = rgbGreen 'Set the colour of the bar in the Beginning
        .EndColour = rgbRed ' Set the colour of the bar at the end
    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
      

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 Class Progress Bar V2.01.xlsm 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.

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.

Advertisements

111 thoughts on “First Class Progress Bar for all your Macros

  1. Ejaz,
    Thank you for sharing. I had some problems get it working because I am using another userform and after that calling the progressbar… at the end I found out that I was closing the first userform too early. Nevertheless thumbs up for your great work.

    Like

  2. Thank you Ejaz, I develop office applications and this progress bar is an excellent tool and have used in a couple of apps that have quite lengthy processes that need to run. Impressive work.

    Liked by 1 person

  3. Great progress tool for Excel. Ran into issue with Windows 10 where and Excel 2016 where color fill for progress has stopped working (worked then something changed with Win 10). I have verified that this is consistent across several Windows 10 machines. Works fine in Windows 7. Anyway, just curious if there are any other instances of this happening.

    Like

    • I recently migrated to Office 2016 on Windows 10 too. I just checked and I am facing the same issue too. This bug happened even on design time. I am able to change to colour of the label, but as soon as I click the title bar of the user form, the colour reverts to that pale grey.

      Microsoft is releasing its operating systems without testing things, and now office 2016 is also beginning to get very buggy.

      God Save Microsoft.

      Like

    • I can confirm too that upgrading to Office 365 2016 caused the progress bar color fill to stop working. It worked fine before the upgrade (Windows 10 + Office 365 2013). Nevertheless thank you for such a useful tool, Ejaz!

      Like

    • Updating to versions 16.0.7465.1015 (Excel 2016), 1610 build 7466.2038 (Excel 2013) and 14.0.7165.5000 (Excel 2010) fixed a lot of visual bugs in Office. Works fine for me now.

      Liked by 1 person

  4. Hi Ejaz, thanks for the progress bar it looks and works amazing! I’ve implemented it into a huge amount of code and for some reason when calling “Myprogressbar.complete 3” at the end of the code it seems to run it twice. The progress bar will say Complete, closing in 3,2,1 and then flash and do it again before actually closing. Not a huge issue but seeing as it looks so good I thought I may as well try and make it look as pro as possible. Do you have any idea why this might be happening? I thought it could be a screen updating thing. Cheers, James.

    Like

  5. Hi there,

    great macro and a nice looking progress bar 🙂

    I was wondering what the terms of use (or the licensing) of your code are? Ist it allowed for companies to use it in their documents (for free)?

    Thank you in advance!

    Regards,
    Sebastian

    Like

    • Feel free to use it in your documents. All the models here are to illustrate ideas and coding practices to learners. If you really like what you are seeing, and would like to support me, I’d invite you to make a donation to keep my blog running.

      I am very grateful that you even took the time to enquire. Much appreciated!

      Like

  6. Hi Ejaz,

    Thanks for this lovely piece of coding.
    I browsed through all the comments but I could not find any mention about how to turn off the close button on top of the form.
    I run some complex macros where they should not be aborted.
    Please guide me on what changes (and where) to make in your code.

    Thanks a lot!

    Like

    • Well, hiding the close button on the form would require some complex windows api programming i suppose.

      You could however, try adding some lines of code in the terminate sub of the form, to check whether the progress bar hit hundred before letting it run fully. This is just a theory. Test it out, and let me know what happens.

      Like

      • OK, a bit of googling helped!

        Source: http://www.excelforum.com/excel-programming-vba-macros/694008-remove-user-form-borders-post2138340.html#post2138340

        And: http://www.excelforum.com/excel-programming-vba-macros/875940-remove-borders-and-x-sign-on-a-userform-through-vba.html#post3874313

        This is what I did:
        I added the following code in ProgressBar Form
        Private Sub UserForm_Activate()
        Me.Height = 0
        RemoveFrame
        Me.Height = 50
        End Sub

        Added another module
        ‘Written: August 01, 2009
        ‘Author: Leith Ross
        ‘Summary: Removes the Titlebar and thick border around a UserForm.

        ‘Returns the Window Handle of the Window that is accepting User input.
        Public Declare Function GetForegroundWindow Lib “user32.dll” () As Long

        Private Declare Function GetWindowLong _
        Lib “user32.dll” _
        Alias “GetWindowLongA” _
        (ByVal hWnd As Long, ByVal nIndex As Long) As Long

        Private Declare Function SetWindowLong _
        Lib “user32.dll” _
        Alias “SetWindowLongA” _
        (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
        Sub RemoveFrame()

        Dim Bitmask As Long
        Dim hWnd As Long
        Dim WindowStyle As Long

        Const GWL_STYLE As Long = (-16)
        Const WS_DLGFRAME As Long = &H400000

        hWnd = GetForegroundWindow
        WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
        
        Bitmask = WindowStyle And (Not WS_DLGFRAME)
        
        Call SetWindowLong(hWnd, GWL_STYLE, Bitmask)
        

        End Sub

        I hope the code appears clean here. Seems to work. Will test a bit more.

        Like

  7. Has anyone else run across an issue when using .Value in conjunction with the progress bar? There’s a variable called “value” within the progressbar code that breaks the .Value property once a progressbar is created. Is there any workarounds for this? I did a simple find-replace “value” with “val” inside the progressbar code and am still running into problems. Any help would be much appreciated.

    Like

    • This progress bar does not have a value property, if i remember correctly.

      And which other value property does it break? The range object?

      Like

  8. I’m thinking of incorporating this onto something I’ve created. The primary function is to extract a number of user set variables from a unformatted data extract sheet and then populate them onto a nice, pretty looking new sheet. The thing is, depending on what is requested by the user, the process could be short or long. I’m struggling to understand if this progress bar will accuratley track progress of my code or whether I input a guesstimate time for when I think the process will finish. Could you advise?

    Like

    • This progress bar cannot be controlled by time taken. This can be controlled only by number of actions completed in relation to number of actions to be performed. The only way I see you using this is to count the number of actions to be performed by looking through your data first and setting the number of actions to be performed. Then you increment the number of actions performed when it is performed.

      I understand that this is a slight waste of resources, but that is the only way you can use this.

      An alternate way, is to consider all possibilities as one action, and you set that as the total number of actions. And you can increment the counter even if you did not have to copy that particular value.

      I hope that helps.

      We have been experiencing unprecedented rainfall and floods here in Chennai; hence the late reply.

      Like

  9. Hi Ejaz,
    i have this macro which delete some sheets from workbook:

    Sub EliminaAlcuniFogli()

    Dim ws As Worksheet
    Application.DisplayAlerts = False
    Call ProgressBar
    For Each ws In Worksheets
    If ws.Name <> “DASHBOARD” And ws.Name <> “RinominaFile” And ws.Name <> “GenerazioneFogli” And ws.Name <> “ListaMail” And ws.Name <> “Sheet2” And ws.Name <> “COPIA SPOSTA FILE CARTELLE” And ws.Name <> “ORIGINAL” And ws.Name <> “UPDATED” And ws.Name <> “TROVA ERRORI” And ws.Name <> “CHANGES” And ws.Name <> “COPIA SPOSTA FILE AVANZATO” Then ws.Delete
    Next
    Application.DisplayAlerts = True
    End Sub

    Now, where can i insert the progressbar call?
    Thank yuo so much.
    Andrew

    Like

  10. Hello Ejaz,

    It is truly a clever way to display and manage progress bars. I’ve been doing the clunky mistakes by using different forms for different parts.
    I implemented your bars quite easily, it is really straightforward to use.

    Thanks for the time that you dedicate in your website, I’ve found multiple useful tricks on it.

    Keep doing the good work!

    Aubry

    Liked by 1 person

    • I am glad you like it. And I am glad you took the time to check out the other posts.

      I spent a lot of time re-building stuff for every project and I got fed up of it. That is why I started thinking of ways to make re-usable projects.

      I want my readers realize that VBA can be super powerful, if you treat it as a programming language, and not a record and play scripting language.

      Thank you for visiting. I hope you will spread work about my little blog. Have a great day.

      Like

  11. i want to say how much style this progress bar adds to any project. however when i call this macro from my project, it runs and after it is complete the next line of code starts-which must be obvious. but then how is it showing any progress in my project? it is in fact taking up more time. or am i missing something?
    i want this progress bar to keep the user occupied till the calculations being done by excel are over but i am not able to achieve that please
    help!
    regards
    vivek

    Like

    • Excel does a lot of things in the background when it calculates. You may want to check out my post about volatile functions to get an idea.

      Only Excel knows exactly what percentage of the calculation is remaining or done. And to find that out will use up resources unnecessarily, and I reckon it is not worth pursuing.

      Note that it does show a progress indicator when it does any calculations in the status bar. However, there is a catch: if your project is enormous, users clicking on the sheet may stop the calculations. And that is precisely why you want to show a progress bar, and stop users from interfering, yes?

      I reckon that the best way to ensure a calculation is made is to Disable screen updating and Events; then run the Application.Calculate or CalculateFull or CalculateFullRebuild methods, in the order of how paranoid you are about ensuring calculations are performed correctly. Don’t forget to enable Screen Updating and events at the end. This will ensure that the user does not stop the calculations inadvertently.

      Unfortunately, I am afraid showing a precise progress bar is not that straight forward. If you are very particular about showing progress, you could just go sheet by sheet and calculate individual sheets. This is very very inefficient, but could fake some sort of an indicator about progress.

      Like

    • There is a drop box link in the post. Click on the file name please. The link might not work if you are trying to access the file from work. Network Admins block cloud services left right and center nowadays.

      Like

        • Thank you for visiting. Do let me know if you need to email the file to you. I haven’t posted anything new in my blog in a while. I am working on a few ideas now; I hope they make you come back here.

          Have a good day.

          Like

    • Hi Kaleb,

      I am happy that you figured it out yourself. I removed the comment you put in earlier because it was super long; I hope you don’t mind.

      I’d remove the current one also if you prefer.

      Thank you for dropping by.

      Regards,
      Ejaz Ahmed

      Like

  12. I really like what you have done with MyProgressbar. Your skills at programming classes are far beyond my own.
    I provided the suggestion to center the progress bar on the worksheet a week or so ago:

    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) – (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height)

    You did a really good job with the classes. I find I can invoke as may progress bars as I want and keep them open until I choose to close them. Each class instance inherits and respects its properties correctly.

    So now I am going to give you a challenge I hope you will take up.

    I’d like to suggest you modify the class code so that when more than one instance is opened they all remain centered in the worksheet. Progress bars that are open would move up a little (-38) and the newly invoked progress bar would be placed below (+75) the ones just moved up.

    I added a calculation to your positioning code that places the second bar below the first. It seems like it would be possible to track how many bars are open and how to position the next one.

    With MyProgressbar2
    .Title = “Aligning Ouput” ‘Optional
    .ExcelStatusBar = True ‘Optional
    .StartColour = rgbMediumSeaGreen ‘Optional
    .EndColour = rgbGreen ‘Optional
    .StartUpPosition = 0 ‘Move the Progress Bar to the center of the worksheet in a dual monitor setting.
    .Left = Application.Left + (0.5 * Application.Width) – (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height) + 75
    .TotalActions = 2
    End With

    It’s the addition of + 75 at the end of the line that moves the second bar down below the first.

    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height) + 75

    Multiple bars work great because of the way you crafted the inheritance.

    Using the + 75 as a reference you might move all open bars up -38 and then drop the new bar in at + 75 from the last invoked bar (lowest bar).

    You may have to limit the number of bars invoked so that they all sit one form. I haven’t thought that through all the way, yet.

    I leave MyProgressbar1 open and invoke MyProgressbar2. As bar2 gets ready to close I use your statement:
    MyProgressbar2.Complete 2

    This closes the second bar and returns focus to MyProgressbar1, which then invokes its command:

    MyProgressbar1.Complete 2

    This closes the first bar.

    It is magic! Multiple MyProgressbars look great and keeps the user informed of the progress of all actions being undertaken. I can easily see invoking five simultaneous bars as processing moves from subroutine to subroutine and then back out.
    Another thought I had is to convert the Excel Color Palette rgbGreen to HTML #FFF00 to give a broader range of colors. I haven’t thought that through yet either.

    You have a first class MyProgressbar (no class inheritance intended) :-).

    CraigM

    Liked by 1 person

    • Hi Craig,

      The joke at the end cracked me up; good one.

      I’d like to take you up on your challenge, but I am afraid I do not have the time now, I have not written any posts here in months and I’d like to give some of my other ideas some time.

      Coming to your comment about colours. I am not using excel’s palette. Excel’s colour pallette is linked to the ColourIndex property of the Interior Object. The Colour property on the other hand uses the Hexadecimal colour system. Also the palette does not apply to UserForms.

      User Forms use the Hexadecimal system just like HTML. You can use VBA’s RGB function to explicitly specify the colour you want, or use any of the predefined constants to pick a colour quickly.

      I recommend you look into the RGB function: https://msdn.microsoft.com/en-us/library/zc1dyw8b(v=vs.90).aspx

      I thank you again for your support and efforts extending my work.

      P.S: I recommend you use the Form’s Height property instead of explicitly specifying it in code, so your code would still work if the use ends up resizing the userform.

      Regards,
      Ejaz Ahmed

      Like

  13. Hello Ejaz,
    nice work…thanks for that, really appreciate it (that save me time and your progressbar looks much better then what I produced 🙂

    I wonder if you could help me.
    My process is the following:
    1. I open a text file count all lines. This number I am using for MyProgressbar.TotalActions
    2. Within my code for e.g. on line 61, I create a batch file –> execute it and an other sub routine tells me, when the external process is done. After that I return and go on to next line 62.
    Is there a way to have maybe another Instance of a second progressbar to show the progress of that sub routine? Or maybe in the statusbar of the progressbar an additional field which tells me the percentage of the sub routine?

    Also many thanks to CraigM for the snippet with the second monitor.

    Thanks a lot Astera

    Liked by 1 person

    • Hi Astera. I am glad that you liked my work.
      There is an example of how to use two instances of the progress bar in the file.

      I suggest you check that out first. Get back to me if you have any more questions.

      Like

      • Hi Ejaz,

        thanks for your quick answer.
        The ProgressBar (one Bar) works like a charm 🙂

        I found the “SubBar” in Class Progress Bar V2.01 in “modCovers”.
        Your example is within Sub TestTheSubBAR()
        I tried a lot to have the MainBar in my Sub and the SubBar in a further public function where I have a Do Loop. But it will not work.

        What I do is:

        In my SUB()
        ‘Declare Sub Level Variables and Objects
        ‘Initialize the Variables and Objects
        ‘Declare the ProgressBar Objects
        ‘Initialize a New Instance of the Progressbars
        ‘Set all the Properties that need to be set before the
        ‘ProgresBar is Shown
        With MainBar
        ……
        End With
        With SubBar
        …..
        End With
        ‘Show the Bar
        MainBar.ShowBar

        in my Do Loop within SUB() I start with
        MainBar.NextAction “Performing Action”

        so far –> OK The MainBar is visible and counts all.

        In my public function()
        ‘Declare the ProgressBar Objects
        ‘Initialize a New Instance of the Progressbars
        Counter = 1
        Do
        ‘Update the ProgressBar NextAction Method
        MainBar.NextAction “Struggling To Excel ” & Counter, True
        SubBar.Terminate —-> at this point I get
        Run-Time Error “Z”:
        Set TotalActions Property First
        When I commet out “MainBar.NextAction “Struggling To Excel ” & Counter, True” then it terminates my SubBar what is ok but my MainBar also !!

        Sorry but I can`t get it to run proper 😦
        Could you please help me a little bit?

        cheers Astera

        Liked by 1 person

        • Hey Astera,

          I need to see where exactly you are declaring the progress bar object.

          The following declaration needs to be outside of any sub in a standard code module:
          Public MainBar as ProgressBar
          Public SubBar as ProgressBar

          I suggest you download the ProgressBar again. I have updated it with a new module “modCovers2” that uses Public Progress Bar objects, and two subs to illustrate how you can modify it from different subs.

          Check it out and get back to me if you have any more questions.

          Regards,
          Ejaz Ahmed

          Like

          • Hi Ejaz,

            thanks for coming back to me.
            I played a little bit at the weekend and found a way. But I don`t know if this is a good way from a programmer perspective.
            I changed ALL “private” statement to “public” within the class “ProgressBar”
            In my MainSub I declared only the MainBar
            In my SubSub I declared only the SubBar.
            To have the MainBar position within the SubSub I created variables like this direct after ShowBar:
            ‘Show the Bar
            MainBar.ShowBar
            mbLeft = MainBar.Left
            mbTop = MainBar.Top
            mbHeight = MainBar.Height
            And used it as parameter to call the SubSub
            (RunShell UPNbat, mbLeft, mbTop, mbHeight)

            If you recommend to change back public to private and use the
            Public MainBar as ProgressBar
            Public SubBar as ProgressBar
            outside my Modules I will give that a try.

            Another little thing I like to mention:
            Your code:
            ‘Move the Second bar below the main Bar
            SubBar.Top = MainBar.Top + MainBar.Height + 10
            SubBar.Left = MainBar.Left

            Whenever the SubBar will be shown first it goes under the MainBar and then to the left. That creates a “twitch” or “flickr” (sorry don`t know how to say in English).
            If you change to have first the “left” statement there is no “twitch”
            SubBar.Left = MainBar.Left
            SubBar.Top = MainBar.Top + MainBar.Height + 10

            cheers Astera

            Like

            • I am happy that it works for you now. Changing the scope of the subs within the class module is not necessary. In fact, I recommend that you don’t change it.

              Some subs were intentionally declared as private so that they are not available to the user, so they do not get confused with options they were not meant to use.

              Like

  14. I just came across this Progress Bar and think it’s great. Very simple to implement when adding the form to the workbook. However, to keep all my workbooks simple and macro free I use a PERSONAL.xlsb for all my macros. I therefore tried to copy the form there and invoke it from PERSONL.xlsb. I know it’s possible to open form from other workbooks but for some reason I can’t get this to work. Any ideas?

    Richard

    Liked by 1 person

  15. Extremely Nice Work! Your ProgressBar is a wonderful addition to my workbooks. It immediately stopped me from clicking the mouse while things were still running.

    The Progress Bar was appearing on the left margin of my second monitor while the worksheet was on my primary monitor. It was very confusing.

    I added the following to move it to the center of the workbook.

    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    

    I added it to the With MyProgressBar section as follows:

    With MyProgressbar
    .Title = “Macro Progress” ‘&amp;quot;Test The Progress Bar&amp;quot; ‘Set the Title
    .ExcelStatusBar = True ‘Set this to true if you want to update Excel’s Status Bar Also
    .StartColour = rgbGreen ‘Set the colour of the bar in the Beginning
    .EndColour = rgbRed ‘ Set the colour of the bar at the end

    'Move the Progress Bar to the center of the worksheet in a dual monitor setting.
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    

    End With

    You did some really nice work with your “MyProgressBar” module. Thank you!

    CraigM

    Liked by 1 person

  16. Hi Ejay!

    Thanks for the code. However, I’m having difficulties incorporating it with a macro. Below is the macro:

    ———–START of CODE————

    Private Sub REVEAL_ALL_TABS()

    ActiveWorkbook.Unprotect Password:=”myPASSword”

    Dim Sh As Worksheet
    Dim yourPassword As String
    yourPassword = “myPASSword”

    For Each Sh In ActiveWorkbook.Worksheets
    Sh.Unprotect Password:=yourPassword
    Next Sh

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws

    Dim Sh1 As Worksheet
    Dim yourPassword1 As String
    yourPassword1 = “myPASSword”

    For Each Sh1 In ActiveWorkbook.Worksheets
    Sh1.Protect Password:=yourPassword1
    Next Sh1

    End Sub

    ———–END of CODE————

    I’ve followed your explanation above, and I get the progress bar to display. But that’s it – the macro does not continue to execute, and the progress bar does not show any progress. It’s as if the userform keeps the focus and nothing happens from there on.

    I’d really appreciate some help on this! Thanks in advance!

    Like

  17. Ejay…. I’m trying to use multiple instances of this Progress Bar… but I keep running into a conflict….

    If I have used the Progress Bar…. and then try to use it again in another instance (which worked previously)… then the Progress Bar Name comes up as an error…

    pbLogNewTrade.ShowBar ‘Critical Line

    I have both the Macro’s in modCovers…. and have Defined the New Progress Bar Name in each….

    I’m trying to make as much sense here as possible…lol…. and am hoping you know what I mean….

    What am I missing here…?

    Thanx

    MikeT

    Like

    • Hi Danilo,

      The Link is alive, but I suspect your network admins have blocked Dropbox. I could send it over to you only by the end of today, but if you need it in urgently, perhaps you could have someone on the outside, mail it to you?

      Regards,
      Ejaz Ahmed

      Like

      • Hi Ejaz,
        It can be possible as I use a corporate machine…
        No, it is not urgent, send me whenever you can! Thanks for responding, tough,

        KR,
        Danilo

        Like

  18. Good job dude! Thx for free share…
    I’m going to change the variable naming convention (I prefer the “Hungarian Notation”) , convert some variables to constants(introduce the errors as string constants e.g. etc) and rearrange the methods and form factory – by introducing a forms handling class maybe.
    Some guys asks for donation for such a lousy work but you would deserve one!

    Liked by 1 person

  19. Hi Ejay

    Im using a very similar code however im hoping that i can get the userform to display each task as a list as it comples them.

    i.e rather than replace the text for each action, list the actions completed.

    Is there any way to amend your code to get this to work? im using excel 2007

    Thank you

    Dan

    Like

    • I developed this to indicate the progress of for loops, hence the current action out of total actions construction. You are looking for something different.

      You could use a list box, and then simply select the task that is being performed at the moment.

      Another option would be, to replace the one line status bar that I have, with a bigger one; and tweak my code slightly to keep appending the messages to the existing text.

      Like

    • I am sorry I answered without fully understanding your concern. Here is what I think you should do:

      Open up my form.
      Get the Name of the Label control I use to display the Status message in the Form.
      Add a new Text box. Give it the name you got in step 2, and delete the label control.
      Identify all the places where I update the Status Bar Label in the Form Module.
      >> LabelName.Caption = “New Text”
      Change the code to the following:
      >>> NewTextBox.Text = NewTextBox.Text & vbnewline & “New Text” >>> Last line has latest update

      or

      NewTextBox.Text = “New Text” & vbnewline & NewTextBox.Text >>> First line has latest update

      This will do the trick. Let me know if it does not work.

      Regards,
      Ejaz Ahmed

      Like

  20. Hello.

    Thanks for sharing the source code of your progress bar. It’s really nice.

    You putted a very good and handful option to allow the progress to be also displayed at Excel’s Status Bar. I would like to know if there is a way to show just the information at Excel’s Status Bar without showing the window of the progress bar itself. If there is no option for doing this currently, can you provide some code that can be inserted at your original code to acomplish this?

    Thank you.

    Liked by 1 person

    • Hi Daniel,

      I am very happy that you like my work.

      Modifying Excel’s status bar is very simple. All you have to do it use the StatusBar property of the Application object.
      Type in: Application.StatusBar = “Your Message”

      Remember to set the property to false when you want Excel to take back control:
      Application.StatusBar = False

      If you are interested in showing the Rectangular boxes you can use the ‘ProgressText’ function, in the ‘ProgressBar’ userform, in the same excel file. Just copy that function into your project, and then call it with appropriate arguments to generate the boxes, as a string, and assign it to the StatusBar property of the Applications object. To indicate a 50% completion, I’d write something like this:
      Applications.StatusBar = ProgressText(5,10) & ” | Your Message here”

      Let me know if you’d like me to explain things in more detail.

      Regards,
      Ejaz Ahmed

      Like

      • Hello Ejaz,

        Your comment maked it more clear for me about how to use Excel’s status bar properly along with your progress bar code.

        In my code I putted an option for the user to choose if he wanted to show your progress bar or not. And if the user sets it to not show the progress bar I inserted this lines along the code in the proper ways:

        lngStatusBarAction = lngStatusBarAction + 1 ‘My declared variable to count Excel’s status bar actions.
        Application.StatusBar = ProgressText(lngStatusBarAction, MyProgressbar.TotalActions) & ” | Message”

        What I wanted was that Excel’s status bar to be always updated, regardless if the user choosed to show or not your progress bar. By doing that I could acomplish this.

        But I missed one thing. Excel’s status bar doesn’t show the percent number (example: 50%) and also doesn’t show the message “Action 5 of 10”, as it does when we set your progress bar to run and update Excel’s status bar. It’s showing only the unicode bar indicator and my message.

        I am doing something wrong? In this manual Excel’s status bar update approach, is it possible to also show the percent number and action count message?

        Thank you!

        Like

        • If you only want to use Excel’s status bar, I’d highly recommend you to not use my Progress bar form. I feel it is very counter productive to use a Form Object, load it into your computer’s memory and not show it.

          All you have to do, to use Excel’s status bar is this:
          Application.StatusBar = “Message here”

          Excel’s status bar shows what ever you ask it to. Therefore to show the Percent Indicator, you have to code it in explicitly:
          Application.Statusbar = Format(lngStatusBarAction * 100 / MyProgressbar.TotalActions, “0”) & ” | ” & …The Unicode Function… & ” | ” & “Your Message”

          I recommend you to slightly modify the Unicode Progress Bar function to generate the Percent Indicator by itself, and then may be make provisions for it to accept the custom message, and then generate the exact text that should be displayed in the Status bar. And then ditch my progress bar completely.

          Something like this
          Function ProgressText(ByVal ActionNumber As Long, _
          ByVal TotalActions As Long, _
          Optional ByVal Message As String = vbNullString, _
          Optional ByVal ShowPercent As Boolean = True, _
          Optional ByVal BarLength As Long = 20)

          Dim BarComplete As Long
          Dim BarInComplete As Long
          Dim BarChar As String
          Dim SpaceChar As String
          Dim TempString As String

          BarChar = ChrW(&H2589)
          SpaceChar = ChrW(&H2000)

          BarLength = Round(BarLength / 2, 0) * 2
          BarComplete = Fix((ActionNumber * BarLength) / TotalActions)
          BarInComplete = BarLength – BarComplete
          ProgressText = String(BarComplete, BarChar) & String(BarInComplete, SpaceChar)

          If ShowPercent Then
          TempString = Format(ActionNumber * 100 / TotalActions, “0”)
          TempString = String(3 – Len(TempString), ” “) & TempString
          ProgressText = ProgressText & ” | ” & TempString & “%”
          End If

          If Len(Message) > 0 Then
          ProgressText = ProgressText & ” | ” & Message
          End If

          End Function

          And Then maybe call it like this:
          Application.StatusBar = ProgressText(Counter, Max, “Your Message” , True)

          Like

          • Hi Ejaz,

            It worked perfectly! Thank you!

            I just needed to add a little piece code in this modified ProgressText function to show the information “Action 50 of 100”.

            Sorry for the delay in answering, I was busy these days. Again, thank you!

            Like

  21. Hi Ejaz
    I have a quick question for you . Is possible to update the Total Action number during the flow?
    I want to add some actions at half way of my code…

    Thanks

    Like

  22. Hello Team, Good Evening. I prepared an excel report for software testing metrics. i want to implement the progress bar untill all the reports generated. please send me the snippet for creating progressbar untill windows 7 progress bar is running.
    Thanks in advance

    Like

  23. Hi Ejaz,

    First off, I just wanted to say a big thank you for all the times your knowledge has helped me.

    I’m really struggling to get this to work in the file I’m using. I have a fairly good understanding of how it all works but it is just that little bit too complex for my tired brain!

    I have a spreadsheet that when the main macro is run, it calls around 15 separate subs to do their thing and would like to use a progress bar such as yours to check on how far it has completed.

    Each sub is called like below:

    Call CombineDataUnapp
    Call SetMonthandTypeUnapp

    Are you able to provide some help as to what I need to do to get this to work?

    Cheers, Josh

    Like

    • This is an excellent question Josh. And I am glad that my blog helped you at work.

      I have not considered this thoroughly, but I reckon you can use a Public ProgressBar object. Initialize it in the Sub that calls all the other Subs, and then try to animate it from the sub-Subs.

      Does that make sense? Give it a shot. Theoritically, it should work. Let me know if you need me to explain it more. If you can’t get it to work, I will give it a try this weekend, and let you know how it goes.

      Regards,
      Ejaz Ahmed

      Liked by 1 person

  24. Hi,

    Looks great! I just can’t figure out how to integrate it into a macro. I tried one simple loop and it ran the full progress bar between each loop. I know it will probably be really simple but it just hasn’t clicked for me yet.

    Like

  25. Hello,

    I dont use any macro in my file so is there any way to use this amazing progress bar when Excel is Actually Calculating? I have managed to use the progress bar after excel finished calculating….or before.. but never at the same time. So how can i trigger the progress bar when sheet change event? or group/ungroup event?

    Thank you.

    Like

    • Thank you can calling my progress bar amazing.
      Which version of excel do you use? Excel 2010 usually displays a progress bar in its status bar.

      Otherwise, calling the progress bar for excel calculations is not that easy. You can write a small macro to find out all the cells that have a formula, and then calculate them one by one. That’s using resources inefficiently, unnecessarily. I wouldn’t suggest that.

      If you want to enforce a full calculation, I can give you a small sub.

      Like

      • Hello,

        I am using Excel 2010. Although i can see the status bar i would prefer the user to see a progress bar or at least a msg box to inform the user that excel is calculating at the same time that excel is actually doing it :).

        I am using a bunch of Indexes formula ( more than 500 rows ) that are searching the data in another sheet ( around 20 .000 rows). Excel is recalculating ,on a sheet change event from a dropdown list or group/ungroup. The actual calculation now last 20-30 sec.

        Thank you.

        Liked by 1 person

        • Well, here is a suggestion:

          Create a User Form
          Set its Name property to “CalculatingForm”
          Set its Caption to “Excel is Calculating”
          Set it ShowModal Property to False
          Simply insert a label control, and set its caption Property to “Calculating…”

          Now, paste the following sub in a Standard Code module and Call it:

          Sub CalculateNow()
          
          DoEvents 'To Make sure the Lines of code following the Show Form Method Execute
          CalculatingForm.Show 'Show The form
          CalculatingForm.Repaint 'Make sure the form is rendered.
          
          Application.CalculateFullRebuild 'Recalculate all the workbooks
          'You can also use this line, if you do not want excel to recalculate dependencies: Application.Calculate
          
          Unload CalculatingForm 'Terminate the form
          MsgBox "Calculation Complete", vbInformation 'Indicate the end of a Successful Calculation
          
          End Sub
          

          This will ensure a full recalculation. Show the Form that says calculating. And it will also let excel show the progress on its status bar. Hope this helps.

          Like

  26. Hi Ejaz. Well done! It looks good. I am still struggling to find uses for Classes. I know they are powerful, but it is finding something that is worth creating them for.

    Regards
    Neale

    Liked by 1 person

    • This project right here is based on what classes can do. The first use I find interesting is, we can create properties and methods and associate some code to it.

      For instance, in this scenario, changing the ActionNumber property of the progress bar animates it.

      Other uses are, you can create a lot of instances; add them to collections, and loop through them.

      Like

  27. Hi there:)
    This seems nice, but it I can’t make it work. Is it right that the &qout; should be replaced with quotation marks(“)?

    Every time I try to run it I get: Run-time error ‘5’ Run the Show Method First

    It seems like there is a problem with line 2 in point 6 “.ActionNuber = 5”

    Do you know what I have done wrong?

    Liked by 1 person

    • You are absolutely right. Remove they have to be double quotes. WordPress editor screwed up again; thanks for letting me know; I will get that taken care of.

      I suggest you take a look at the cover macros I have written in the standard code module in the file attached to the download link.

      Like

  28. I wrote one for Access this spring. That was before I realized you could use userforms in Access. I digress though, I mention it because I have a way to run it in a loop, waiting for a query to run. I thought you may be interested. http://christopherjmcclellan.wordpress.com/2014/03/08/progress-bar-for-ms-access/

    Also, I was looking at your code, and I really think that you should take a look at Enums for your error codes. It will help you clean up all of those magic numbers. Swing by Code Review sometime and we’ll help you clean it up a bit. http://codereview.stackexchange.com/

    Liked by 1 person

  29. Very cool progress bar! I really need to start using them as they make code execution look a lot more professional. Can definitely relate with your (old) feelings toward class modules. I still haven’t fully embraced them but I’ve be able to hash out a few cool things with their vast capabilities.

    Liked by 1 person

  30. Pingback: Progress Bar for all your excel Applications #Excel #VBA #UserForm | Struggling To Excel

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s