Use Excel VBA code to display a progress splash screen while performing a task

[nandagopal]

The following code displays the splash screen UserForm. It first disables keyboard input.

Code Select Expand

Next the code creates the form, sets the form's public TaskDone variable to False, and displays the form non-modally. It then enters a loop where it simulates a long task. Each time through the outer loop, the code updates the form's progress bar prgStatus so the user can see that the program is doing something.

After the task is complete, the code sets the form's TaskDone variable to True and closes the form. It then re-enables keyboard input.


Private Sub cmdShowSplash_Click()
Dim frm As frmSplash
Dim i As Integer
Dim j As Integer

    ' Deactivate the keyboard.
    Application.OnKey "^d", "KeyboardOn"
    Application.DataEntryMode = True

    ' Display the splash form non-modally.
    Set frm = New frmSplash
    frm.TaskDone = False
    frm.prgStatus.Value = 0
    frm.Show False

    ' Perform the long task.
    For i = 0 To 100 Step 10
        frm.prgStatus.Value = i

        ' Waste some time.
        For j = 1 To 1000
            DoEvents
        Next j
    Next i

    ' Close the splash form.
    frm.TaskDone = True
    Unload frm

    ' Re-activate the keyboard.
    Application.DataEntryMode = False
End Sub


The UserForm contains the following code. Variables TaskDone indicates whether the long task is complete. The QueryClose event handler uses it to decide whether it should allow the form to close.


' Set true when the long task is done.
Public TaskDone As Boolean

Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    Cancel = Not TaskDone
End Sub