davesexcel.com

Excel information

Most Popular Excel Questions

Make a screen Pop Up when Opening Excel

Posted by davesexcel on April 14, 2012 at 3:30 PM

I want a screen to pop up when I Open an Excel Workbook and then the screen closes after 5 seconds.


This can be done by creating a UserForm, and then use VBA code to Show the userform.(Splash Screen)

We use 3 types of modules here

  1. TheWorkbook Module
  2. The UserForm Module
  3. The Regular Module

First create the Userform that you want to pop up when you open the workbook.

In the VBA editor at the Top Menu bar, select Insert=>Userform

Select the Label Control from the Toobox and draw it on the userform. When you have the label selected you can go into the labels Properties window and set the text font you want to display when the UserForm Pops up.



In the Workbook Module enter this code.

Private Sub Workbook_Open()

    With UserForm1

        .StartUpPosition = 0

        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)

        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

        .Show

    End With

End Sub

In the Userform Module enter this code.

Private Sub UserForm_Initialize()

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

End Sub

Private Sub UserForm_QueryClose _

        (Cancel As Integer, CloseMode As Integer)

'Stop the use of the close button

    If CloseMode = vbFormControlMenu Then

        MsgBox "You cannot use the close button"

        Cancel = True

    End If

End Sub

 

 

In a Regular Module use this code

Sub UnloadScreen()

    Unload UserForm1

End Sub

 

 Save and close the workbook, then open it.


If you do not know how to create a UserForm check out this link.

How to Create A UserForm

Checkout the examples

Make Screen Pop Up on Open Using xl 97_03

For newer versions

Excel 2007 and Newer

Categories: None

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

You must be a member to comment on this page. Sign In or Register

0 Comments