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.

Figure 7-3. Procedure drop-down box for the various events of the UserForm object

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.

Figure 7-4. Example splash screen in action

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.