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
- 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.
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.
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.
Thanks Chris. Class modules are so awesome.
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/
Thanks for the invitation. I will check out the Code Review thing. And I will check out the Access Progress bar and let you know what I think.
Nice work Ejaz
Thanks Mark.
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?
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.
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
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.
Hi, Id like to download your macro but I cannot open the link in our office. hope you can send me a copy in my email?
sandramiles02@gmail.com
I have sent you an email. Be sure to check your spam, if it did not show up in your inbox.
thank you. i got it 🙂
Hi,
I cannot download the file in dropbox, looks like it was blocked. can you send the file in my email?
sandramiles02@gmail.com
Thanks!!
Hi There,
It was nice to see such a use of VAB.Very Well done.
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.
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.
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.
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:
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.
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.
Hi Danny, I need to see your code to check what’s happening. There is a simple loop in the workbook, that explains how to use the bar. Go through it again. If you still need my help, mail your code to me: StrugglingToExcel@outlook.com
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
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
Hi Ejaz,
I’ve spent the morning trying to get it to work but no luck unfortunately. All I seem to be able to do is get my system to hang repeatedly!
I’ve put a copy of the main module up on dropbox (https://www.dropbox.com/s/l82sbg2uhn40qfm/Module1.bas?dl=0). If you do find a chance to have a quick look, I would be forever in your debt.
Thanks again,
Josh
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
I do not understand your question. Please give me more details. The Windows 7 Progress bar part confuese me the most.
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
You Could Go into the ProgressBar Class module and comment out the part where I do not let you change that attribute.
Hi, seems to be great work ! Could you send it to me by mail as well, please ? alex90@noos.fr Thanks !
Hi Alex, I have sent you the file. And accepted your request on LinkedIn. Let’s keep in touch.
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.
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
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!
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)
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!
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
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.
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
This will do the trick. Let me know if it does not work.
Regards,
Ejaz Ahmed
Awesome class module! Thank you.
You are most welcome!
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!
I am curious to know what a form handling class is.
I learnt about using enums to display errors after this project. Is that what you mean?
I’ve been playing with that thing and i ve reworked it a little bit. I hope you will not be angry about that…
see then the change log in the user form, but i guess, the most significant change is the possibility to associated the progress pointer directly with the loop pointer, and the new Interface class to provide the user only relevant properties and methods
I would be glad about some feedback best regards!
https://www.dropbox.com/s/wb67hnq3g6ajdsf/Class%20Progress%20Bar%20V4.01.xlsm?dl=0
I created an interface class first, and then decided to move to this set up, because it allowed me to move the subsequent progress bars below the promary progress bar quickly. But I agree that a intermediate class helps beginners use the progress bar.
Absolutely first class….. thanx for the share…. really…100%
Thank you!
Hi there! The DB hyperlink is not live. Would you send me the form via email? carneirodanilo@live.com
Thx 🙂
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
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
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
Ag…. sorry…. I just need to create new form (ProgressBar1, ProgressBar2…etc) for each instance….
Sh1t…. that did not actually work….
What am I missing here Ejay…? It’s driving me nuts…
Sorry to bug you….
The mod cover module has an example in there that would help you create multiple instances. Give it another read. If you still are running into trouble, mail me your code: StrugglingToExcel@outlook.com
I shall take a look at it over the weekend.
It was simply a “Runtime Error ‘401’: Can’t show non-modal form when modal form is displayed.” The easy fix was to simply Hide the previous form before loading the ProgressBar form…..
My mistake… I should have taken more notice of the error message….
Love this Progress Bar…. works like a charm….
I am glad that you were able to fix the problem yourself. That is sort of my mission: help people not feel intimidated by VBA. You Sir, just made my day.
Another point I’d like to make here is that this setup is capable of displaying two progress bars simultaneously. If you want to do that, and not able to, then let me know. I’d help out. I’d need to see your code though.
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!
Hi Jaco. I dont see any lines here relating to displaying the progress bar. Please add them in and then I’d be happy to help. Thank you for showing interest in my progress bar.
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.
I added it to the With MyProgressBar section as follows:
With MyProgressbar
.Title = “Macro Progress” ‘"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
You did some really nice work with your “MyProgressBar” module. Thank you!
CraigM
I am glad you like it. I am a single monitor guy, didn’t consider the problem you faced. Sounds like a fine addition. Thank you for improving upon my work. Much appreciated.
Thanx CraigM…. that’s going to be handy to include “Just in case…”
Cheers
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
I need to look at how you are invoking the form to get an idea.
I suggest you look into creating an addin. I reckon is definitely better than using a binary file, in your case. You can even look into creating a simple ribbon to invoke the macros.
Richard, you’ll need to change the class’s instancing property to “Public – Not Creatable” and add a regular module with some pubic factory methods that return a new instance of the class. https://support.microsoft.com/en-us/kb/555159
Works a treat very easy to follow instructions as well. Got it up and running within 20 minutes. Good that you included some sample code as well in its usage as that helped a lot
Glad it helped Steve!
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
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.
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
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
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
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.
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
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
Never mind I figured it out… Now I feel stupid
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
Any current download links, or just a pastebin available for this code?
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.
Thanks, dropbox must be blocked on my network like you said.
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.
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
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.
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
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.
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
There is an example in the workbook. Check it out. I suggest you look at another post in my blog, which has a progress bar that does not use classes: Normal Progress Bar
That workbook has illustrative cover macros in it too.
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?
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.
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.
This progress bar does not have a value property, if i remember correctly.
And which other value property does it break? The range object?
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!
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.
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
End Sub
I hope the code appears clean here. Seems to work. Will test a bit more.
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
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!
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.
This is strange. Let me look into it.
Would you confirm whether the same thing happened even if you run one of my cover macro examples?
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.
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.
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!
Thank you for sharing your experience here. And thank you for the feedback. Hope you come back and find something useful.
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.
Thank you for letting me know.
This looks great but I do not know how to use it in my codes 🙁
Did you try looking at the cover macros in the Workbook?
Work it out Chema…. it’s worth the effort…
On 30 October 2016 at 16:18, Struggling To Excel wrote:
> Ejaz commented: “Did you try looking at the cover macros in the Workbook?” >
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.
Thank you. It has been a while since I posted anything. I’m hoping to change that this year. Wish me luck!
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.
Thank you for taking the time to leave a comment. Much appreciated!
Ejaz,
Thanks for sharing this progress bar…
Will I be able to use this form when excel is exporting some data to word?
If you have a total number of actions, and if you can call my macro after each action gets over, you can use it for anything.
Great work! Learned a lot on how using Objects. THANKS!
Glad I was able to help!
How do i make progress bar for connection update. I have one that show percent in status bar but not know how to add it to the form. Please help
Sub RefreshAllConnections()
Dim con As String
Dim cLen As Long
Dim Iter As Long
Dim pInt As Double
Dim percent As String
cLen = ActiveWorkbook.Connections.Count
For Iter = 1 To cLen
pInt = Iter / cLen
percent = FormatPercent(pInt, 0)
With ActiveWorkbook.Connections(Iter)
Application.StatusBar = “Updating ” & Iter & ” of ” & cLen & ” | ” & percent & ” Complete”
.Refresh
End With
Next Iter
End Sub
You have to declare and initialise the progress bar object first. And instead of updating the status bar, you have to call my object’s methods.
There are lots of notes on how to use my class in the post and on the example macros in the workbook. Give it another read. Write back if you have any questions.
I am not sure how to do that as i am a beginner . Refresh all connections is general bar that can be used widely. Will you be able to help creating one please.
Try to look at my cover macros and ask me what you don’t understand about it specifically. I’d be glad to help you understand.
This is a blog on helping people learn how to use VBA. I prefer not to hand you solutions. I aspire to creating learning opportunities.
Hello Ejaz.
Can you please give me your email address ?
I’ll ask about this progress bar in here but script VBA for the imolementation is very long.
Thank you,
Haris
You can mail me at: contact@strugglingtoexcel.com
hi Ejaz,
thank you, I have sent you an e-mail, please reply,
thank you
Haris
Hi Ejaz,
I’m a new subscribed and also i’m very newbie with VBA, I have a question :
Let say i download and follow the procedure to install in my excel 2016 the progress bar in worksheet called ‘MYWorksheet’.
then i send ‘MYWorksheet’ to a friend, The progress bar is going to work on his computer with excel 2010 ? or
he have to download follow procedure to install the progress bar on his computer?
If you’ve successfully imported it into your workbook, it travels with the workbook. It will work on any computer. It should work on Excel 2007 and later.
My advice for you is, these are the kind of questions you can easily answer yourself by experimenting. Have fun while you learn my friend! All the best!
Hi Ejaz,
Thank you for fast response. and btw Yes i know, trying is the best way to learn but my comment/question was set as example.
Thank you
Best regards
Hello Ejaz, Great tutorial. BTW i have a bunch macros. I am running all macros using call function. How to use is tutorial for my requirement. i.e without loop.
This is very cool … but is it me? Or shouldn’t there be (if only by virtue of the project’s name and intent) an actual Class Module contained in the project? There are only two general VBA Modules.
I see what you mean. In the interest of making it compact and easy to install, I used the form itself as a class.
I got feedback from other readers that a form’s built-in properties and methods confuse a new user, when Intellisense is turned on.
I don’t think you’re a “pompous prick”, Contrary i think you’re a humble person that try to share your knowledge and help lot people….god bless you my friend.
Regards
Andy
You made my day Andy. Thank you! God bless you too!
It’s pretty amazing your work, I will try to understand the code, the class code into the form, impressive. Thanks for sharing
Good luck with understanding the code. Do ask me any questions you may have.
Thank you for the efforts!
Is it possible to have my counter equal a variable that is in my coding. For instance I have a variable z which goes through 3 loops because each loop is a different product (1 – 200, than 201 to 400 and than 401-600). I want my counter to change everytime z changes. It is kind of working but it is changing the outcome of my actual data. My code looks like this:
Sub Run()
Application.ScreenUpdating = False
DeleteContents
Dim z As Integer
Dim Counter As Long
Dim TotalCount As Long
Dim SomeRange As Range
Dim EachCell As Range
TotalCount = 600
Set SomeRange = Sheet1.UsedRange
SomeRange.Clear
Dim MyProgressbar As ProgressBar
Set MyProgressbar = New ProgressBar
With MyProgressbar
.Title = “Test The Bar”
.ExcelStatusBar = False
.StartColour = rgbMediumSeaGreen
.EndColour = rgbGreen
.TotalActions = TotalCount
End With
Debug.Print MyProgressbar.Title
Debug.Print MyProgressbar.TotalActions
Debug.Print MyProgressbar.ActionNumber
Debug.Print MyProgressbar.StatusMessage
MyProgressbar.ShowBar
Counter = z
For z = 1 To 200
‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
DoEvents
End If
MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
Next z
For z = 201 To 400
‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
DoEvents
End If
MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
Next z
For z = 401 To 600
‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
DoEvents
End If
MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
Next z
MyProgressbar.Complete 3
SomeRange.Clear
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
You can set it to a variable. It’s just that you can’t change it after setting it once.
You have to determine the total number of actions in advance.
I tried to download Class Progress Bar V2.02.xlsm, I get the following error
“Error (429)
This link is temporarily disabled. The person who shared it hit their daily limit of traffic or downloads. Learn about traffic ”
can you please help? 🙂
Hi Ammar. This is the first time Dropbox is limiting downloads. I was not aware of this. I am looking into this, I’m considering other places to host my data. For the time being, please write to me at ejaz@strugglingtoexcel.com. I’ll email you.
I just want to say Thank you!!!, recall the comment of Steve Bayliss its exactly my situation “l 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.” I just would add that people get anxious to wait, so this tool keep them relax. I used your old version, this is even more impressive with the sub main. Thanks for your effort.
Hi
Found it by random and like it.
However, I have a few comments and suggestions for improvement 😉
1) Downloading shows version 2.02, but in Macro there is no version but just a date.
Not a prob but you have to download again to compare the date …
2) Sometimes you may abort own script before or after initialization of the bar.
When jumping than to end of own script and trying to close the bar in case of open, you get a problem when trying to execute Terminate, as Bar might have not been shown.
To handle this, I’ve added a new property “isShown”, which returns me the cFormShowStatus variable.
With this I can now wrap my complete and terminate methods at the end of the script and it works now for any of my cases.
3) Sometimes, I do not know how many total actions I need.
As I can not change the total actions amount after ShowBar execution, I have to handle that in each call of ActionNumber increasement or NextAction execution. Which is annoying …
To handle this, I’ve added in both a simple logic like if cActionNumber >= cTotalActions, then cActionNumber = cTotalActions
Due to I never run into problems with ActionNumber overflows.
Might not be perfect but good enough for dynamic processes with unknown number of steps to be executed.
4) At the end of the process, before I run Complete method, I have to set the ActionNumber to TotalActions, in case it is lower. As otherwise I get an error.
Why ….. ?
I changed it by simply run NextActions in a loop till TotalActions are reached.
To still keep origin behaviour, I add a third bool param to handle the loop instead of default error handling.
Might be helpfull for others too
Thank you very much for your kind readiness in making available your code. It is very grateful to see that the altruist and cooperative spirit have not fled away from the big net.
Again,
Thank you very, very much!
I am glad to see the spirit of being grateful has not disappeared either. Thank you for taking the time to appreciate my work. I hope you’ll share it with your friends and colleagues as well.
Hi – I only discovered your site by FINALLY reading the comments of the code I’ve taken from Rob Bovey/Stephen Bullen implementation of your progress bar.
They’ve taken it a bit further and used an Interface Class with the progress bar.
I realized that using the class did allow me to create multiple progress bars. At first I was doing the same as you – that I had to grab the Main bars top/height to determine the position of the Sub bar. This is fine if the Main and Sub bars are with in the same procedure, but if you have a main procedure that’s calling multiple sub procedures (and those call other procedures) that need progress indicators, that became difficult to do.
I finally created a collection called ProgressBarStack that held the position of the next progress bar. Basically, every time a progress bar is created, it adds the next position in the collection, when a progress bar is finished, it removes it’s position from the stack (which is always the last one in the collection). So all the positioning is done within the progress bar class.
Now obviously, if you have too many progress bars running, they could be written off the screen – luckily I haven’t reached that point yet. It could be done by holding each progress bar in a collection and every time a new one is written, move the previous progress bar up the screen a bit. Not sure how much overhead/flashing of the screen would occur doing this though.
Just a thought!
Thanks again for this awesome “reusable progress bar object”!
That is a fine addition! Thank you for extending my work. I feel so guilty that I haven’t been actively publishing recently, but moments like these are inspiring. Let me know if you are interested in sharing your work with my readers.
Hello Ejaz
Hope all Fine.. at your end
Need a small help from you. i want to use your progress bar to run with my Recorded Macro. but dont have any idea how to proceed . i have imported userform and Module from your excel sheet. can u pls guide me how to apply your progress bar to my recorded macro.
Thanks a Million in adavnce