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)
.Repaint
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
module:
Sub ShowUserForm()
With UserForm1
.LabelProgress.Width = 0
.Show
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. _
ThemeColorScheme.Colors(msoThemeAccent1)
 
Search JabSto ::




Custom Search