Creating a Splash Screen | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Some developers like to display some introductory information when the application is opened. This is commonly known as a splash screen. You are undoubtedly familiar with Excel's splash screen, which appears for a few seconds when Excel is loading.

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 seconds.

CD-ROM

The companion CD-ROM contains a workbook that demonstrates this procedure. The file is named splash screen.xlsm .

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

Create your workbook.
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 .
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 14-4 shows an example.

Figure 14-4: This splash screen is displayed briefly when the workbook is opened.
Insert the following procedure into the code module for the ThisWorkbook object:
```
 Private Sub Workbook_Open()     UserForm1.Show End Sub 
```
Insert the following procedure into the code module for UserForm1 . For something other than a five-second delay, change the argument for the TimeValue function.
```
 Private Sub UserForm_Activate()     Application.OnTime Now + _       TimeValue("00:00:05"), "KillTheForm" End Sub 
```
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.
As an option, you can add a small CommandButton named CancelButton , set its Cancel property to True , and insert the following event handler procedure in the UserForm's code module:
```
 Private Sub CancelButton_Click()     KillTheForm End Sub 
```
Doing so lets the user cancel the splash screen before the time has expired by pressing Esc. You can even place this small button behind another object so it won't be visible.

Caution

Keep in mind that the splash screen is not displayed until the workbook is entirely loaded. In other words, if you would like to display the splash screen to give the user something to look at while the workbook is loading, this technique won't fill the bill.

Tip	If your application needs to run some VBA procedures at startup, you can display the UserForm modeless so that the code will continue running while the UserForm is displayed. To do so, change the `Workbook_Open` procedure as follows :

 Private Sub Workbook_Open()         UserForm1.Show vbModeless         ' other code goes here     End Sub