Excel VBA Macro: Progress Bar While Macro is Running (UserForm)

Показать описание

Excel VBA Macro: Progress Bar While Macro is Running (UserForm). In this video, we go over how to create a status bar that serves as a progress indicator for any users running your macro. The progress bar we make is created with a UserForm that updates as the macro continues to run. Each update shows an increase in bar width and percentage. We also briefly go over the importance of DoEvents and vbModeless

Data used in this video:

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub delete_rows()

Dim count As Integer
Dim start As Integer
Dim i As Integer

i = 2

OpenStatusBar

Do While i LT= count

If Cells(i, 9) = "" Then

Rows(i).EntireRow.Delete
i = i - 1

End If

DoEvents
Call RunStatusBar(i, count)

i = i + 1

Loop

Unload StatusBar

End Sub

Sub OpenStatusBar()

With StatusBar
.Bar.Width = 0
.Frame.Caption = "0% Complete"
.Show vbModeless
End With

End Sub

Sub RunStatusBar(row As Integer, total As Integer)

With StatusBar
.Bar.Width = 246 * (row / total)
.Frame.Caption = Round((row / total) * 100, 0) & "% Complete"
End With

End Sub

#ExcelVBA #ExcelMacro

greggowaffles

Рекомендации по теме

Комментарии

Great explanation, really understood everything pretty easily.. better than other tutorials on progressbar in vba

ajazsky

Thank you very much. Simple and effective.

Ericvl

Is it possible to run the status bar in the video without looping command?

fajarpradhana

Dear sir, your video tutorial is very important for Excel learner, so a request to you, my application, Need multiple files name show when data consolidated in progress bar or status bar, could you please help me, it's very important to me, may you help me, thank you, ❤

mostainbillah

I use for next in my code, where i place the "doevents" ?

a.irfansyah

Do you know the impact on computational time something like this has on the program?

aiden

I’ve been looking for this for a while.... amazing video! For my macro I have to track progress of several Queries refresh, is there a way to track that in vba? Thx

heglem

greggowaffles, you saved me with this ".Show vbModeless"!!! Thx a lot

jpjacoby

Mate that looks so good, am definitely gonna use this technique on my macros going forward. Great video!

Nutflash

Excelente, desde Perú un fuerte abrazo, con esto concluyo mi proyecto !

GaelExcelFacil

Great video! Works perfectly for me. While I watched the first time, was wondering why you didn't dim row and total (I'm not a programmer). I tried on my code using the exact same code you share and the Sub RunStatusBar did not work, but it was just a matter of using the variables I've already used instead of row and total and worked perfectly. Thanks!

maocabezas

Thanks very much for the tutorial. I have a personal macro file where I have a code to give me a list of sheets' name. Since it's in my personal macro file, I can run that code in any active workbook and it'll give me the list of sheets' name in that workbook. Is it possible to create a progress bar for that code? Just like a global code, can there be a global progress bar which is attached to the code and will run on any workbook the moment I run the code?

panksimus

As i am new to the coding world i am facing some difficulties to place a progressbar while macro runs ( progressbar for add, delete, update and search from userform to sheet). could you please help me with that if i send you my file?

charlessteve

super video! you helped a log. God bless you :)

dimanezhdanov

Thanks so much, I was getting quite frustrated as I found lots of examples online but not one explained exactly how to incorporate my macro; after watching your video it worked first time and is absolutely perfect!!!

DrunkenKnight