Live Updating Loading Bar

This is a Loading Bar that allows you to indicate on screen how far along the process your VBA code is currently at – and it’s incredibly easy to add to your existing working files.

[responsive]Live Updating Loading Bar[/responsive]

Give the end-user live updating background information without completely changing your existing code.

[hr style=”3″]

How do I add this to my workbook?

  1. Extract the contents of the ZIP file into an easy location (e.g. Desktop, Documents etc.)
  2. Open Excel and load the [tooltip text=”Alt+F11″]VBA Editor[/tooltip]
  3. Right-click in any white space in the Project Explorer Pane (on the left)
  4. Click on Import File
  5. Navigate to the folder with the contents of the zip file
  6. First import: f_load.frm (this is the userform)
  7. Repeat steps 3 to 5 and this time import UpdateLB.bas
  8. Your code and userform is now imported into your file.
  9. To display the userform while your existing code is running, and the following line at any point
    •  Call UpdateLoadingBar(10,"Step 1 Complete",TRUE) 
    • See the section below called “How does it update” for details regarding the parameters
  10. Save the workbook as an XLSM or XLSB file to retain the code

[hr style=”3″]

VBA Code

Option Explicit

Sub UpdateLoadingBar(LoadingPercent As Variant, UpdateText As String, DisplayPercent As Boolean)

'For further tips or questions, visit FormatCells.com
'http://www.formatcells.com/excel-development/live-updating-loading-bar/

With f_load

Dim OriginalWidth As Double

'#################################################################
OriginalWidth = 300
' THIS IS THE ORIGINAL WIDTH OF THE LOADING BAR
' IF YOU CHANGE THE WIDTH OF THE BAR, UPDATE IT HERE!
'#################################################################

'ENSURE A NUMBER IS PASSED THROUGH
If IsNumeric(LoadingPercent) = False Then
MsgBox "Invalid Percentage Value.", vbCritical
Exit Sub
End If

'THIS WILL DISPLAY THE USERFORM IF IT IS NOT ALREADY VISIBLE
If .Visible = False Then

'MAKE USERFORM MODAL IN 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)
.Show False

'FIRST LOAD - CLEAR EVERYTHING
.img_load.Width = 0
.lb_percent.Caption = ""
.lb_update.Caption = ""
.Repaint

End If

'THE LOADING BAR WIDTH = ORIGINAL WIDTH / 100 (FOR 1%) * LOADING PERCENT
.img_load.Width = (OriginalWidth / 100) * LoadingPercent

'UPDATE TEXT - SEND "" IF NO TEXT REQUIRED
.lb_update.Caption = UpdateText

'DISPLAY THE UPDATE PERCENT OPTIONALLY
If DisplayPercent = True Then
.lb_percent.Caption = LoadingPercent & "%"
Else
.lb_percent.Caption = ""
End If

.Repaint

'IF IT HAS REACHED 100%, THEN HIDE THE USERFORM
If LoadingPercent >= 100 Then Unload f_load

End With

End Sub

'#####################################################################################################
' EVERYTHING UP TO AND INCLUDING THE NEXT HASHED LINE IS FOR EXAMPLE PURPOSES ONLY AND CAN BE DELETED

Sub Update()

Call UpdateLoadingBar(10, "Starting...", True)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(20, "Extract email attachments from your Outlook Inbox", True)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(30, "Iterate through data looking for specific criteria and copy to another location", True)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(40, "Process calculations, create lists, and generate exceptions", False)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(50, "Email documents to specified distribution groups", False)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(60, "Convert files and save in a drop location for upload", False)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(70, "Generate files based on multiple data sources", False)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(80, "Open Internet Explorer, navigate, and input data into specific fields", True)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(90, "Submit forms and send confirmation emails", True)
Application.Wait Now + #12:00:02 AM#

Call UpdateLoadingBar(100, "", True)

End Sub

' EVERYTHING FROM THE NEXT LINE UPTO THE HASHED LINE IS FOR EXAMPLE PURPOSES ONLY AND CAN BE DELETED
'#################################################################################################### 

Quick Questions

Toggle
[accordion-item title=”Is this free?”]YES! Completley![/accordion-item]
[accordion-item title=”Why/When would I use this?”]Does your code take time to update or finish? Utilise this time to credit the work that is going on behind the scenes!

  • Keep the user updated.
  • Display a funny joke or two
  • Anything to make the calculation process more user friendly[/accordion-item]

[accordion-item title=”How does it update?”]You invoke the code by calling it via a Sub and passing through the parameters i.e.

 Call UpdateLoadingBar(10,"Step 1 Complete",TRUE) 

The 10 stands for 10% complete (you can send any value), Step 1 Complete is the text to display under the loading bar, and the TRUE displays the percentage on the form – sending FALSE hides the percentage value on the form.

 

You can add the line anywhere you want in the code whener you want it to update, and add the Sub it references to in it’s own module (or in an existing one).[/accordion-item]

[accordion-item title=”How much can I change if I do not know how to edit the code?”]You can change the appearance of the form i.e. position, layout, colours, logos (by replacing default one, not adding a new one) etc. but you cannot delete the items or change the width of the loading bar.  All other changes require code changes.

You can hide text from under the loading bar by sending “” instead of “Step 1…”, and hide the percent updated for by sending FALSE instead of TRUE.[/accordion-item]

[accordion-item title=”What numbers can I display as a percentage?”]Whatever you want, even decimals, to any degree! However, sending 100 or higher will close the form.[/accordion-item]
[accordion-item title=”Can I change the colours?”]YES! The colours do not affect the functionality of the loading bar.[/accordion-item]
[accordion-item title=”Can I turn on/off the percentage permanently?”]Yes, but you will need to add/remove lines in the code directly.[/accordion-item]
[accordion-item title=”Can I add my own logo!”]YES! Be sure to save it as a JPEG though.[/accordion-item]
[accordion-item title=”Wait, how does your logo have a grey background?”]Paint. RGB(240,240,240)[/accordion-item]
[accordion-item title=”Does it slow down my VBA code?”]On an Intel Core i7 machine, with the loading bar active – that did everything you can see in the GIF above – it took one extra second.[/accordion-item]