VBA Express : Excel - Use Standard MS Progress Bars on Userform

Excel

Use Standard MS Progress Bars on Userform

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

A userform with two standard MS progress indicators will be shown. These progress bars will cycle through a simple demonstration procedure and the userform unloaded.

Discussion:

Often when a lengthy procedure is running you want the user to be given some indication of the progress. This can be done in several ways, but the most common method is to use progress bars (i.e. 'progress indicators'). To get a really professional 'look' to your project you can use Microsofts own progress bars, or, if you want to be different, you can create custom bars, as in this entry here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=169 or, alternatively, you can use the status bar as a progress indicator as shown here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=87

Code:

instructions for use

			 '*****************************************************
 '<< CODE FOR STANDARD MODULE >>
 
Option Explicit 
 
Private Sub AddReference() 
    On Error Resume Next '< error = reference already set
     'set reference to Microsoft Windows Common Controls 6.0 (SP4)
     '(aka: Microsoft Progress Bar Control 6.0 (SP4))
    ThisWorkbook.VBProject.References.AddFromGuid _ 
    "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}", 2, 0 
End Sub 
 
Sub ShowForm() 
    Call AddReference 
    UserForm1.Show 
End Sub 
 
 '*****************************************************
 
 
 
 '*****************************************************
 '<< CODE FOR USERFORM1 CODE MODULE >>
Option Explicit 
 
Private Sub UserForm_Activate() 
    Dim M&, N& 
     '*******PROPERTIES*******
    With UserForm1 
         '(set form properties)
        .Height = 120 
        .Width = 380 
        .Caption = "My Progress Indicators" 
    End With 
    With ProgressBar1 
         '(set prog.bar1 properties)
        .Height = 15 
        .Width = 355 
        .Left = 10 
        .Top = 30 
        .Min = 0 
        .Max = 100 
        .Scrolling = ccScrollingSmooth 
    End With 
    With ProgressBar2 
         '(set prog.bar2 properties)
        .Height = 15 
        .Width = 355 
        .Left = 10 
        .Top = 75 
        .Min = 0 
        .Max = 40 
        .Scrolling = ccScrollingStandard 
    End With 
    With Label1 
         '(set label1 properties)
        .Height = 15 
        .Width = 130 
        .Left = 10 
        .Top = 15 
    End With 
    With Label2 
         '(set label2 properties)
        .Height = 15 
        .Width = 130 
        .Left = 10 
        .Top = 60 
    End With 
     '************************
     '*******PROGRESS********
    For M = 1 To 40 
        For N = 1 To 100 
            ProgressBar1 = N 
            Label1 = "Individual Progress = " & N & "%" 
            DoEvents 
        Next N 
        ProgressBar2 = M 
        Label2 = "Over-all Progress = " & M * 2.5 \ 1 & "%" 
        DoEvents 
    Next M 
     '************************
    Unload Me 
End Sub 
 '*****************************************************

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
Select Insert/Module, copy and paste the code (above) for the standard module in this modules code pane
Put the mouse pointer anywhere inside the code for 'Private Sub AddReference()'
Now click Run/Run Sub/UserForm in the VBE toolbar (this sets a reference)
Select Insert/Userform (this will create Userform1)
Select 'label' in the toolbox showing, now put the mouse pointer near the top left of the Userform and move the pointer down and to the right (this will create a label)
Select 'label' again and repeat (we want two labels)
On the toolbox, select 'Progress Bar' and (similarly) move the pointer down and to the right to create a progress bar
Repeat this (we want two progress bars)
Double-click the userform and a code pane will appear
Delete any code in this code pane and copy and paste the code for Userform1 from above
Now select File/Close and Return To Microsoft Excel
Save your work

Test the code:

Select Tools/Macro/Macros.../ShowForm/Run

Sample File:

MSprogressBars.zip 14.49KB

Approved by mdmackillop

This entry has been viewed 464 times.