What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleUse Excel VBA code to display a progress splash screen while performing a long task
DescriptionThis example shows how to use Excel VBA code to display a splash screen while performing a long task.
KeywordsExcel, VBA, splash screen, progress
CategoriesOffice, Software Engineering
The following code displays the splash screen UserForm. It first disables keyboard input.

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
        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
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.