Progress window with text updates in Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates how to create a progress window with text updates in Excel using VBA (Visual Basic for Applications).

This post explains how to display the progress window with text updates as shown in the picture below -

Consider the application user interface (UI) is designed as -

This application functionality is user browses a folder and clicks on Import button. On clicking import button, the application processes all the files in a folder. After import, the user clicks on Export button, which exports the worksheet data as a PDF report.

Check this link how to generate a PDF report from an excel workbook or worksheet - Generate PDF

It is very important to show updates or status of the application to the user when the application takes a significant amount of time for code completion to improve user interface.

Now we look into the code how to achieve this functionality.

Step 1: Design the form as shown in the picture.

Select properties of a form (Press F4 or  View -> 'Properties Window') : update the Name of a form to frm_Progress and Caption to "Processing input files".

Select properties of a label: Update the Name to "lblProgress". In addition you can add the label text color, background colour, default text and resizing options of a label text in the properties.

Step 2: Add "Import" button event code. 

In this application all buttons are of command type.

In an excel application, double-click the Import button to generate an event handler code. The code looks as follows:

Private Sub cmdImport_Click()

    frm_Progress.Show

End Sub

Here the progress window displays immediately when the user clicks on Import button. You can change this behaviour by adding some validations or some other work to be done before popping up the progress window.

Step 3: Add the following code in the UserForm_Activate event.

To generate event handler code of a form; double-click on the form (not on label) to generate the UserForm_Activate Code. The code looks like -

Private Sub UserForm_Activate()
    
    'DoEvents force the application to trigger the form events
    DoEvents
    'the application calling the following sub-routine
    'change the name of a sub-routine as per your need
    Call Processing

End Sub

Step 4: Add the following code in a sub-routine "Processing".

Then it is the time to add a code in the Processing sub-routine which tells you how to update the progress window.

Sub Processing()

    Dim MyPath As String
    Dim fso As Object
    Dim Files As Object
    Dim File As Object
    
    'error handler
    On Error GoTo Error_Handler
    
    'Update the label with a status message
    With frm_Progress
        .lblProgress.Caption = "Processing data" & vbLf & vbLf & "Please wait..."
        .Repaint
    End With

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Files = fso.GetFolder("C:\Test").Files

    For Each File In Files
        With frm_Progress
            .lblProgress.Caption = "Processing " & intFileCount & " of " & Files.Count & " file(s)" & vbLf & vbLf & File.Name
            .Repaint
        End With
        
        'Doing some file processing
        
    End If

ExitRtn:
    'close the form at the end
    Unload frm_Progress
    Set fso = Nothing
    Set Files = Nothing
    Set File = Nothing
    Exit Sub

Error_Handler:
    MsgBox Err.Description
    Resume ExitRtn
End Sub

This sub-routine is performing the task to iterate through each file in a browsed folder and doing some file processing. Here the code is just for your understanding. So here the progress window displays the status of the application with a message saying that which file is processing of total files.

.lblProgress.Caption is to set the label message.
.Repaint is to update the form label message and continues to run the sub-routine.

It is very important to close the progress window in case of error or at the end of the sub-routine. Else the progress window stays on even the application completes the execution of code.

If you want to hide or show the progress window in between use the following code -

    'to hide the progress window
    frm_Progress.Hide
    'to show the progress window
    frm_Progress.Show

You have to follow these 4 steps to show the progress window with text updates.

Following is the sample progress window that displays while the applications runs -

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.