Some More about Custom UserForms

Creating a Splash Screen

Some developers like to display some introductory information when the application is opened. This display is commonly known as a splash screen.

You can create a splash screen for your Excel application with a UserForm. This example is essentially a UserForm that displays automatically and then dismisses itself after five or ten seconds.

Follow these instructions to create a splash screen for your project:

1. Create your workbook.
2. Activate the Visual Basic Editor (VBE) and insert a new UserForm into the project.The code in this example assumes that this form is named UserForm1.
3. Place any controls that you like on UserForm1. For example, you may want to insert an Image control that has your company’s logo.

Figure Below is the example when Excel open file named as Splash Screen

4. Insert the following procedure into the code module for the ThisWorkbook object:

Private Sub Workbook_Open()

UserForm1.Show

End Sub

5. Insert the following procedure into the code module for UserForm1. For a delay other than five seconds, change the argument for the TimeValue function.

Private Sub UserForm_Activate()

Application.OnTime Now + _

TimeValue(“00:00:10”), “KillTheForm”

End Sub

6. Insert the following procedure into a general VBA module:

Private Sub KillTheForm()

Unload UserForm1

End Sub

When the workbook is opened, the Workbook_Open procedure is executed. The procedure in Step 4 displays the UserForm. At that time, the UserForm’s Activate event occurs, which triggers the UserForm_Activate procedure (see Step 5). This procedure uses the OnTime method of the Application object to execute a procedure named KillTheForm at a particular time. In this case, the time is five seconds after the activation event. The KillTheForm procedure simply unloads the UserForm.

Using a ListBox in a UserForm

Create a Form as shown in figure.

This UserForm contain a Label, a ListBox, Two Command Button one for Run and One for Cancel.

Add a CommandButton ActiveX Control on Sheet1. As shown in below figure.

Add Below mentions code for Command Button which is on Sheet1.

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Add Below mentions code for Command Button (Run) which is on UserForm1.

Private Sub RunButton_Click()

  Select Case ListBox1.ListIndex

Case -1

  MsgBox "Select a macro from the list."

Exit Sub

Case 0: Call Macro1

Case 1: Call Macro2

Case 2: Call Macro3

Case 3: Call Macro4

Case 4: Call Macro5

Case 5: Call Macro6

End Select

Unload Me

End Sub

Add Below mentions code for Command Button (Cancle) which is on UserForm1.

Private Sub CancelButton_Click()

Unload UserForm1

End Sub

Below is the method to initialize UserForm

Private Sub UserForm_Initialize()

With ListBox1

.AddItem "Fill Series from 1 to 10"

.AddItem "Fill Red Color"

.AddItem "Fill Orange Color"

.AddItem "Fill Green Color"

.AddItem "Fill Blue Color"

.AddItem "Fill Purple Color"

End With

End Sub

In Command Button (Run) we called macro named as Macro1, Macro2… etc. Below is the code for VBA general module.

Sub Macro1()

    Range("A6").Select

    ActiveCell.FormulaR1C1 = "1"

    Range("A7").Select

    ActiveCell.FormulaR1C1 = "=+R[-1]C+1"

    Range("A7").Select

    Selection.Copy

    Range("A7:A15").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("A6").Select

End Sub

Sub Macro2()

    Range("B6:H14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Sub Macro3()

    Range("B6:H14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 49407

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Sub Macro4()

    Range("B6:H14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 5287936

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Sub Macro5()

    Range("B6:H14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 12611584

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Sub Macro6()

    Range("B6:H14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 10498160

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub