How to Set a progress bar in Excel VBA

1. in the Visual Basic Editor, select insert-user form to generate a userform1, from the attribute bar in the lower right corner (right-click to select the pop-up attribute bar ), set caption to "simulated operation ......" (This is a title. You can name it by yourself.) Set width to 350 and Height to 50.

2. Then you need to insert four Label labels (that is, the upper-case letter A in the control toolbar, such as 1 ).

3. Place Label1 on the Right of UserForm1, set caption to null, BackColor to white, SpecialEffect to 2-fmspecialpolictsunken, Width = 285, and Height = 15.

4. Place Label2 on the left of Label1, closely following Label1, and set caption to "progress". You can choose the color as needed (set in Forecolor)

5. Label3 is placed inside Label1, closely following its left side, caption is set to null, BackColor is set to blue, Height = 15, Width = 20

6. label4 caption is set to 0%, ForeColor is set to "red" (of course you can set the color you like), BackStyle is set to 0-fnbackstyletransparent (that is, set the attribute to transparent ), height = 15, Width = 20. See figure 2.

7. Select Insert-module, and write a macro here. In fact, the Label3 width is controlled through an operation to display its progress. The calculation in the macro is just an example. You can set the denominator of the calculation progress based on the number of cycles you run.

Figure 1

 

Figure 2

 

 

Macros to be compiled:

Public Sub Macro_1 ()

Total = 100000

With UserForm1

. Show 0

For I = 1 To total

. Label3.Width = Int (I/total * 285)

. Label4.Caption = CStr (Int (I/total * 100) + "%"

DoEvents

Next I

End

End Sub

 

Note: doevents must exist; otherwise, the progress bar will be invisible. As for how many cycles are run, we need to look at your own program. 100000 is just an example. You only need to embed your program into your own loop to display your own progress bar.

You can set other attributes on your own. I just gave you an example.