Excel Hacks Ebook [Electronic resources]

Hack 85 Create a Workbook Splash Screen

Splash screens provide that extra bit of polish to an applicationnot to mention that they keep you entertained while the application loads. Why shouldn't a spreadsheet do the same?

You can use Excel's VBA capabilities to create a splash screen for any workbook; you'll find the process is easier than you might imagine it would be.

To create a splash screen that shows for 5 to 10 seconds when a workbook opens, then closes itself automatically, start by pressing Alt/Option-F11, or select Tools Macro Visual Basic Editor to open the VBE. Then select Insert UserForm. If the Control toolbox is not showing, select View Toolbox to view it.

From the toolbox, left-click the Label control. (Hover your mouse pointer over each control to display its name.) Left-click anywhere on the UserForm to insert the label. Using the size handles, drag out the label so that you can type some brief text into it. With the label still selected, left-click again. If the label is not selected, slowly double-click it. You should now be in Edit mode and should be able to highlight the default caption Label1.

Within that label, enter the text My Splash Screen. To change other properties of the label (its font size, color, etc.), ensure that the label is selected and press F4, or select View Properties Window. Then change the required property in the Label Controls Property window. Now double-click the UserForm (not the label) and then select Initialize from the Procedure box at the top right of the screen, as shown in Figure 7-3.

Within this procedure, enter the following:

Application.OnTime Now + TimeValue("00:00:05"), "KillForm"

Your code for the UserForm should look like this:

Private Sub UserForm_Initialize( )
Application.OnTime Now + TimeValue("00:00:05"), "KillForm"
End Sub

Next, select Insert Module, and enter the following code exactly as shown:

Sub KillForm( )
Unload UserForm1
End Sub

Now all you need is some code in the private module of the Workbook object (ThisWorkbook). In the Project Explorer, you should see the name of your workbook. Expand the folders branching off the bottom of the workbook until you see ThisWorkbook under Microsoft Excel Objects. Double-click ThisWorkbook to expose its private module.

In the private module of the ThisWorkbook object, enter the following:

Private Sub Workbook_Open( )
UserForm1.Show
End Sub

Close the window to get back to Excel. Save and close the workbook, and reopen it to see your splash screen in action. Figure 7-4 shows an example.

Just remember that the splash screen should show for only a short period of time and should contain brief but relevant text. Showing it for longer than 10 seconds might annoy users.