Sunday, June 19, 2011

How to Create Progress Bar in VBA


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.
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.
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.
4. Type the following to create a new variable and define its value:Dim cnt As Integercnt = 0
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
6. Type the following to create a while loop and iterate 10 times while pausing the code for 5 seconds:Do While cnt
10Select Case cntCase Is = 1Me.Label1.Visible = TrueMe.Label1.BackColor = vbBlueCase Is = 2Me.Label2.Visible = TrueMe.Label2.BackColor = vbBlueCase Is = 3Me.Label3.Visible = TrueMe.Label3.BackColor = vbBlueCase Is = 4Me.Label4.Visible = TrueMe.Label4.BackColor = vbBlueCase Is = 5Me.Label5.Visible = TrueMe.Label5.BackColor = vbBlueCase Is = 6Me.Label6.Visible = TrueMe.Label6.BackColor = vbBlueCase Is = 7Me.Label7.Visible = TrueMe.Label7.BackColor = vbBlueCase Is = 8Me.Label8.Visible = TrueMe.Label8.BackColor = vbBlueCase Is = 9Me.Label9.Visible = TrueMe.Label9.BackColor = vbBlueCase Is = 10Me.Label10.Visible = TrueMe.Label10.BackColor = vbBlueEnd SelectMe.RepaintApplication.Wait Now TimeValue('00:00:05')cnt = cnt 1Loop
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.

Blogger news