Microsoft Office Tutorials and References
In Depth Information
Displaying a Progress Indicator
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload UserForm1
End Sub
The GenerateRandomNumbers procedure contains two loops. Within the inner loop is a call to
the UpdateProgress procedure, which takes one argument (the PctDone variable, which
represents the progress of the macro). PctDone will contain a value between 0 and 100 .
Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, “0%”)
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
End With
End Sub
Creating the start-up procedure for a stand-alone progress indicator
All that’s missing is a procedure to display the UserForm. Enter the following procedure in a VBA
Sub ShowUserForm()
With UserForm1
.LabelProgress.Width = 0
End With
End Sub
An additional accoutrement is to make the progress bar color match the workbook’s
current theme. To do so, just add this statement to the ShowUserForm procedure:
.LabelProgress.BackColor = ActiveWorkbook.Theme. _
Search JabSto ::

Custom Search