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. 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
3. Place any controls that you like on UserForml. For example, you may want to insert an Image control that has your company's logo. Figure 14-4 shows an example.
4. Insert the following procedure into the code module for the ThisWorkbook object:
Private Sub Workbook_Open()
UserForml.Show End Sub
5. Insert the following procedure into the code module for UserForml (this assumes a five-second delay):
Private Sub UserForm_Activate() Application.OnTime Now + _
TimeValue("00:00:05"), "KillTheForm" End Sub
6. Insert the following procedure into a general VBA module:
Private Sub KillTheForm()
When the workbook is opened, the Workbook_Open procedure is executed. This procedure displays the UserForm. At that time, its Activate event occurs, which triggers the UserForm_Activate procedure. 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.
7. 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()
Doing so lets the user cancel the splash screen before the time has expired by pressing Esc. You can stash this small button behind another object so it won't be visible.
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.
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() UserForml.Show vbModeles ' other code goes here End Sub
Was this article helpful?