How to Create Progress Bar in VBA

Use Excel and VBA to create a progress bar.

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 Integer cnt = 0

Step 5

Type the following to hide all the labels when the Form starts:

Me.Label1.Visible = False Me.Label2.Visible = False Me.Label3.Visible = False Me.Label4.Visible = False Me.Label5.Visible = False Me.Label6.Visible = False Me.Label7.Visible = False Me.Label8.Visible = False Me.Label9.Visible = False Me.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 + 1

Loop

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.Show End Sub

Press "F5" to run your program.

references