Excel VBA Progress Bar on Userform

[@sanssatori]

Something happened which has never happened watching anybody else's tutorials. It worked the first time!!!! Thank for your clear and concise video, this was really nice.

[@Computergaga]

You're very welcome.

[@wickedlikethreesixes5167]

This really helped me out a lot, thank you. Before this video I found code online for implementing progress bars and it was well beyond my programming capabilities.

[@Computergaga]

Great to hear. Thank you Tim.

[@PHobbs]

I tend to overthink things, and was thinking a project like this would be more difficult. Thank you for the tutorial, and showing me how easy this script is.

[@Computergaga]

You're welcome buddy.

[@dennismorley1647]

Great explanation and demonstration and easy to follow. Have been meaning to install this feature but have always thought it too difficult, and then within minutes I had it running in my macro - I'm so chuffed now. Many thanks - so grateful for your contribution.

[@Computergaga]

You are welcome! Thank you Dennis.

[@noeliamontero3839]

Great explanation for VBA beginners, thanks so much!

[@Computergaga]

Thank you, Noelia.

[@philipmcdonnell7168]

Thanks Alan. This was one of the first videos of your that I watched and until today have never made it work (my brain being somewhat addled). Today, however, I got it to work: I had to slot the contents of the Do While into a For Next loop that loads records into a worksheet from a db and formats them for UX reasons. Now I just have to get tricky with it. ;)

[@georgeloizides7555]

Very Illustrative Example . Thank you very much sir!

[@Computergaga]

You're welcome. Thank you.

[@abrieventer157]

Thanx for this easy to understand video. Method is easier than others I have seen. Thanx again.

[@Computergaga]

My pleasure. Thank you Abrie.

[@JanBolhuis]

Great video and well explained. Thank you.

[@Computergaga]

Thank you, Jan.

[@LuckVagner]

Very easy to understand, great job!!!

[@Computergaga]

Thank you Luca.

[@anthonyfisiihoi]

Great tutorial and explanation. Clear and precise! *thumbs up*

[@Computergaga]

Thank you, Anthony.

[@minhafathima9535]

Great Sir, you are my super hero

[@Computergaga]

😊

[@kennytait8431]

Thanks a lot, this is a great bit of code and your video is really easy to follow. I had a userform connected to a macro that just displayed the comment "Calculating ....... Please Wait", but this is so much better. I simply added a bar as you described into my existing userform. My only problem was that I have no loop in my macro. Solved that by simply adding the update lines with a new width / percentage number at various stages of my code.

[@Computergaga]

Awesome, well done Kenny.

[@mohammadarif4153]

Excellent...very helpful....

[@Computergaga]

Thank you, Mohammad.

[@prayan72]

Thank you! Great video. Is there a way to put the button "cancel" in the form so that if it takes too long the user can abort the procedure?

[@evandawson1037]

This is just fabulous! Just exactly what i expected to do for my project!

[@Computergaga]

Fantastic!

[@KleanthisSkoulikaris]

WOW! Just....WOW!!!

[@Computergaga]

👍

[@kennethschuman167]

Great video! I worked along with several progress bar video’s but with yours was simple AND mainly I got it to work, SO MANY THANKS. One question; after the progress bar macro ends there is a 5 second delay as my full macro updates and ends. Can this 5 sec. delay be incorporated in the progress bar display?

[@ДмитрийПахомов-б6у]

Thanks a lot! So easy!

[@Computergaga]

You're welcome.

[@johnadam7996]

thank you for this amazing tutorial

[@Computergaga]

You're welcome. Thank you John.

[@HerculesSantosLoyola]

Parabéns pela aula, eu achei incrível.

[@Computergaga]

Thank you.

[@HerculesSantosLoyola]

@@Computergaga embora eu não fale Inglês, consegui perfeitamente entender a sua aula e executar com sucesso a barra de progresso. Parabéns pelo seu ótimo trabalho, sou muito grato!

[@javierlira6033]

wonderfull... i made a similar subroutine but I used the statusbar... but it was a desperate solution with the clock ticking... thanks for the video, i will improve my program with it

[@Computergaga]

Thank you Javier.

[@hungskya820l7]

Very Good. Thanks

[@Computergaga]

Thank you.

[@aymen.zaggar]

We can also divide progressbar.width by progressframe.width to get the percent without adding another variable

[@Computergaga]

Great

[@aposieczanek]

What do you do if the % of 100% complete is unknown? or undefined? Are you still able to use a progress bar?

[@user-yy6vn7ig2d]

Can it be done on a range as well? To copy range cells from sheet to another. For example A2:C60

[@johnleo6419]

I have a question: I’d like to keep a bunch of stuff in my userform (100s of frames, labels and other objects) which are overlapping on each other. So It’s becoming very difficult to work on those, is there a way we can hide/unhide the frames on the userfom ?? Because every frame I use covers the entire userform, and there are many objects inside each frame. So I wanted hide all other frames except the one I’m working on, and again I’ll hide this and unhide another frame which I’ll work on and so on… I’m not asking to hide/unhide these frames after running the VBA, I know how to do that. I want to know if it’s possible to hide while we are coding

[@simxschl]

This progress bar works well but the macro seems to take MINUTES longer (700 sec instead of 8 sec normally) if I use "Application.ScreenUpdating = False". If I don't use "Application.ScreenUpdating = False" then it's about 18 sec which is kinda okay but I don't like that flickering of the macro when it switches between worksheets

[@tonnievaneldik9183]

How can i use this ProgressBar in my files so it appear when i run a macro. I don't understand how it can be work on my files.

[@sisoader7391]

GOOD JOB.THANK YOU. HOW CAN I USE THIS PROGRESSBAR IN ACCESS TO IMPORT EXCEL FILES. THANKS

[@Computergaga]

Thank you. It all works from the loop. So, whatever macro you are creating, set the loop to track the progress how you need it. I can' provide all the code here.

[@canmanttv3306]

Great video! Just one problem, I did basically the exact same thing you did in the video, but trying to make the form in the personal workbook, as well as the module is in the personal workbook. I am wondering if this is even possible. When I call "Progress", it says "file not found" and then error 361. I have no idea if this is even possible, but please help!

[@canmanttv3306]

Nevermind, I got it figured out the next day thank you so much

[@Sysco360]

It's wizardly when you make genius look like child's play. I am a rank noob but would like to add the progress bar to display while a SQL Query refreshes after clicking a button assigned to Macro calling Refresh.

[@Computergaga]

Thanks Adam.

[@Sysco360]

Most welcome! :) but guess I should have been more clear... I was hoping for some help with that... if it's not too big a deal?

[@Computergaga]

To update the progress bar we would need a stage to frequently update it. If the SQL refresh is one statement, I'm not sure how we can do this. For example, in the video we can check the update through the loop and have a rough estimate of progress. Unless we could query how far through the refresh the SQL query is, we cannot estimate this.

[@Sysco360]

Thanks for the reply Alan. I actually understand what you are saying, which is something. What if I knew that the query takes on average say 4min 35sec and really does not deviate from that amount of time. Would it be possible to tie the progress bar to that, but also have it terminate early if the SQL query finishes before that estimated time? p.s. Like to buy you a coffee card for your help or 'donate' somehow. your videos are priceless sir! Do you have Starbucks or a donation account?

[@Computergaga]

Hi Adam, not something I have done so am unsure how to update based on time. I do not have a donation card. Thank you for your kind offer.

[@Elchimbotanito69]

Thank you! Lo pude utilizar en mi aplicativo. Saludos de Perú

[@Computergaga]

De nada, Walter.

[@sudiptaghosh326]

Sir first of all thank u so much for your valuable training. Sir I made a Inventory Software based on excel which has 6 sheets... those are ITEM LIST MASTER COPY, INVOICE GENERATOR, RECEIVED LIST, ISSUED LIST, CURRENT STOCK AND SALES REPORT.... all are inter connected. In invoice template I can bill 20 items at a time. This template has few macro buttons like stock update, save as pdf, sales report, new invoice etc... When I run Stock update button, My "Issued List" sheet updates with sales item and also items deducts from Current Stock Sheet which shows balance stock. Sir my problem is When I am entering items in Invoice template for billing its very hard to track each items balance stock... My question is sir Is there any options to make such a display which will show the current stock of that particular item which ĺ enter in invoice template for billing???

[@Computergaga]

Absolutely. As you enter the item number we can run a search and return the stock level into a cell.

[@mithunjan18]

How should a make this pgress bar working if i dont have any Do while or Each cell loop in my macro.... i have 19 modules in my macro and all 18 are called from module 1. Is it possible to run this progress bar whenever each module completed..... ?

[@Computergaga]

For sure. You just need to increment the bar a little after each module. I used a loop, it could be anything. 100/18 and increment by that percentage each time as a rough guide to progress.

[@SandeepKumar-sb3pq]

Awesome video, thanks, but I do have one question , I have got almost 20 different columns which has 20 different loops running , so for each loop I have to introduce the call function ? So if one loop finishes (I.e code gets executed for one column) then won’t the progress bar be 100% and be misleading ? Thanks

[@Computergaga]

You can update the progress bar whenever you want Sandeep so that it is as accurate as you want it to be. So you could update it after each call function. So it updates 5% after each call for example. It is just a guide to progress.

[@SandeepKumar-sb3pq]

Thank you Sir for your reply, i finally figured it out with your help :)

[@PHobbs]

Sandeep, I would imagine you could calculate the percentage of total cells looped through rather than the total rows that Computergaga does in this video. So if each column has 1000 rows of data to be looped through, and you're looping through 5 columns, you would just calculate the percentage of 5000 cells total that you're looping through.

[@sanssatori]

​@@Computergaga This is EXACTLY what I need. Can you give me some more guidance or direction on how to do this?

[@RAragao]

@@SandeepKumar-sb3pq would you be kind to let us now how did you use this code for more than 1 loop through several columns?

[@BrewMAJret]

This is great. You have a single line of code encapsulated by the Progress Bar script. I like to put things into my Personal.xlsb like your progress bar. I have several instances where the progress bar would be cool, but it would not be practical to encapsulate each of my macros with it. Is there a way to call the progress bar script from my existing macros?

[@Computergaga]

Absolutely. The progress bar script can be stored in your Personal.xlsb and run using Application.Run(Personal.xlsb!ProgressBar") This could open your userform. Depending on the macro and what it does, you may have to tweak how it calculates the macro progress though.

[@BrewMAJret]

Thanks for your quick reply. BTW, I follow you on Twitter and your Tweet caught my attention, and your excellent video intrigued me. I looked at several other Progress Bar videos and yours was by far the best one. To further explain my question, your solution encapsulates your actual macro (increase each value in column J by 10%) with the progress bar. If I were to call a progress bar macro via the application.run(personal.xlsb!ProgressBar) macro, then how would I insert my actual macro (instead of increasing values in column J by 10%) back into the ProgressBar macro? Macro A calls ProgressBar macro, but ProgressBar needs to track the progress of macro A or B or ... I don't really have a project for this. I was just trying to take your method to the next step and make it portable either by making it an addon, function or macro in my personal.xlsb. Sorry if all this is outside the scope of a KZbin comment :)

[@Computergaga]

Thank you very much for your comments. Much appreciated. Yes to generalise the macro by putting it into a Personal.xlsb file is difficult because the method of calculating macro progress would differ depending on what the macro does. You may be able to have the Progress form in Personal.xlsb and the sub with the userform initialisation and the variables for bar.width and text.caption. But the progress calculation would probably have to happen in the sub of the active workbook.

[@richardgreves6161]

@@Computergaga I tried this, and while the sub is called properly (the form is initialized and shows up on the screen, the BarWidth variable crashes (BarWidth = Progress.Border.Width * CurrentProgress). It says object required

[@hokh5003]

Hi, how about progress bar when we loop through the file in the folder?

[@Computergaga]

Yes, you can do this with the same technique demonstrated in the video. Maybe count the number of files in the folder and then use that as your percent to work through.

[@vivekkashyap7732]

Great.. My code is without loops.. I call many subs in my code.. How can I use this without loop.. please help

[@Computergaga]

You could maybe use the sub calls as progress. It depends on the macro and how you would like to show updates. Loops are an obvious choice. But if you have say 4 sub calls. you could update the progress bar by 25% each time.

[@satyenpurohit3947]

Can we increase speed of progress bar, if yes, then how

[@Computergaga]

The speed of the progress bar is determined by the macro and how long it takes. We could speed it up by making it update less often. So in my video example, get it to update every other row.

[@priya.8170]

Its not Working kindly help me

[@Computergaga]

I can't help without seeing what might be wrong. Please check through the video again to look for any mistakes.

[@raysomnaths]

SSS [Simple Smart Solution]

[@Computergaga]

👍

[@kanavkohli794]

Too confusing, you need to explain it clearer

[@Computergaga]

Very sorry Kanav.

[@kanavkohli794]

@@Computergaga eh its ok. Im kind of a noob anyway 🤪

[@Computergaga]

Keep plugging away Kanav.