|
|
|
|
|
|
|
|
|
Excel
|
Custom Dual Progress Bar
|
|
|
Ease of Use
|
Hard
|
|
Version tested with
|
2002
|
|
Submitted by:
|
Jacob Hilderbrand
|
|
Description:
|
This macro demonstrates how to use a UserForm as a progress bar.
|
|
Discussion:
|
You have a macro that takes several minutes to run. You want to have a progress bar appear to show the status to the user. This example uses a UserForm and has two seperate progress bars. One for overall progress and one for secondary status. In the example, nothing is being calculated in the loops, however, you can easily add any macro that uses a loop.
|
|
Code:
|
instructions for use
|
Option Explicit
Sub ShowForm()
ProgressBar.Show
End Sub
Option Explicit
Private Sub UserForm_Activate()
Application.Cursor = xlWait
ProgressBar.MousePointer = fmMousePointerHourGlass
DoEvents
Call CalculateData
Application.Cursor = xlDefault
Unload Me
End Sub
Private Sub UserForm_Initialize()
TextBox2.Left = TextBox1.Left
TextBox2.Top = TextBox1.Top + 3
TextBox4.Left = TextBox3.Left
TextBox4.Top = TextBox3.Top + 3
TextBox2.Width = 0
TextBox4.Width = 0
End Sub
Sub CalculateData()
Dim Total1 As Long
Dim Total2 As Long
Dim x As Long
Dim y As Long
Dim MyTimer As Double
Total1 = 20
Total2 = 1000
For x = 1 To Total1
For y = 1 To Total2
MyTimer = Timer
ProgressBar.TextBox4.Width = (y / Total2) * 200
ProgressBar.Label2.Caption = "Calculating Data: " & y & " of " & Total2
DoEvents
Next y
ProgressBar.TextBox2.Width = (x / Total1) * 200
ProgressBar.Label1.Caption = "Updating: " & x & " of " & Total1
Next x
End Sub
|
|
How to use:
|
- Open Excel.
- Alt + F11 to open the VBE.
- Insert | Module.
- Paste the code from above that is designated for the Module there.
- Insert | UserForm.
- Double click the UserForm to view the code window.
- Paste the code from above that is designated for the UserForm there.
- Add Two Labels, and 4 Text Boxes to the UserForm from the Control Toolbox.
- Download the attachment to see an example of this UserForm.
|
|
Test the code:
|
- Tools | Macro | Macros...
- Select ShowForm and press Run.
|
|
Sample File:
|
Progress Bar.ZIP 8.53KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 3209 times.
|
|
|