How to Create Progress Bar in VBA

By Jaime Avelar

A progress bar is used in a computer application to indicate the progress of an operation. It's commonly a rectangle that's animated as an operation makes progress. Visual Basic for Applications, or VBA, is a computer programming language used in Microsoft Office Applications to automate routine tasks and to create user interfaces. You can easily create a progress bar using VBA in Microsoft Office Excel. Excel is a spreadsheet application included in the Microsoft Office suite.

Step 1

Open Microsoft Office Excel, then press "Alt" and "F11" to open the Visual Basic Editor. Right-click "VBAProject," then click "UserForm" to insert a new Form. From the "Toolbox" menu, click "Label." While holding your mouse button, drag on the Form to create a new label control.

Step 2

Right-click the label and select "Properties," then change "Height" to 42 and "Width" to 12. Change the "Top" to 12. Create nine more labels using the same process.

Step 3

Click "CommandButton" on the "Toolbox" pane and add a new button to your Form. Right-click "CommandButton" and click "View Code" to create a new procedure.

Step 4

Type the following to create a new variable and define its value:Dim cnt As Integercnt = 0

Step 5

Type the following to hide all the labels when the Form starts:Me.Label1.Visible = FalseMe.Label2.Visible = FalseMe.Label3.Visible = FalseMe.Label4.Visible = FalseMe.Label5.Visible = FalseMe.Label6.Visible = FalseMe.Label7.Visible = FalseMe.Label8.Visible = FalseMe.Label9.Visible = FalseMe.Label10.Visible = False

Step 6

Type the following to create a while loop and iterate 10 times while pausing the code for 5 seconds:Do While cnt <> 10 Select Case cnt Case Is = 1 Me.Label1.Visible = True Me.Label1.BackColor = vbBlue Case Is = 2 Me.Label2.Visible = True Me.Label2.BackColor = vbBlue Case Is = 3 Me.Label3.Visible = True Me.Label3.BackColor = vbBlue Case Is = 4 Me.Label4.Visible = True Me.Label4.BackColor = vbBlue Case Is = 5 Me.Label5.Visible = True Me.Label5.BackColor = vbBlue Case Is = 6 Me.Label6.Visible = True Me.Label6.BackColor = vbBlue Case Is = 7 Me.Label7.Visible = True Me.Label7.BackColor = vbBlue Case Is = 8 Me.Label8.Visible = True Me.Label8.BackColor = vbBlue Case Is = 9 Me.Label9.Visible = True Me.Label9.BackColor = vbBlue Case Is = 10 Me.Label10.Visible = True Me.Label10.BackColor = vbBlue End Select Me.Repaint Application.Wait Now + TimeValue("00:00:05") cnt = cnt + 1Loop

Step 7

Click the "Insert" menu then click "Module" to add a new module. Copy and paste the code below to start your "UserForm1.":Sub showProgressBar() UserForm1.ShowEnd SubPress "F5" to run your program.