May 9, 2011, 10:57 a.m.
posted by donnie
Create a Workbook Splash Screen
Splash screens provide that extra bit of polish to an application—not to mention that they keep you entertained while the application loads. Why shouldn't a spreadsheet do the same?
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.
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 shows an example.