Welcome Message in Application

Let us try a small trick with VBA.  How about a welcome message pops up automatically when you open your Excel Workbook and a Goodbye message before closing the Workbook?

We assume that you are using Microsoft Excel 2007.

1. Open one of your existing Excel Workbooks or create a new Workbook to try this out.
2. Click on the Visual Basic option under Developer Menu to open the VBA Module Window.
3. Check the VBA Window image given below.

The VBA Window is divided into two parts.  The top window area shows the list of Worksheets and other objects in the active Workbook.  The Code area below is where we can write Code to automate certain actions so that working with the Excel Application becomes easy to the Users and interesting too.  You can see one line default code Option Explicit (says needs to define variables before they are referenced in programs) is appearing in the code window area.

If you look closely on the top window area you can see that Sheet1 (mySheet) Object is in selected state.  The name in bracket (mySheet) is appearing because I renamed Sheet1 as such.

1. Click on the drop-down control, where the text (General) is shown, to display the Object type selected in the above window.
2. Select Worksheet from the displayed list. You can see an empty skeleton of Worksheet_SelectionChange() Sub-Routine is added as default to the VBA Editing window. But, we don’t need that for now.
3. Click on the right side drop-down control to display the available event procedure’s list, which can be selected and programmed on the worksheet object to automate worksheet related actions.  We are not going to write any code within the default program block added into the VBA editing window.  You may delete the opening and end statements of the procedure of Worksheet_SelectionChange() Sub-Routine.
4. Now, click on the ThisWorkbook object from the Window above to select it.
5. Select Workbook in the (General) Control from the drop down control.

The opening and closing statements of the Open Event Procedure of the Workbook is added to the VBA Editing window as default.  You can see that the action type Open is selected as default in the right side drop-down control.  Whatever action code we write within the Workbook_Open() Event Procedure will be executed automatically and immediately on opening the Workbook.  We will write a single line of code to display a welcome message when the workbook is open.  Modify the code as shown below by adding the middle line with the MsgBox() Function.

Private Sub Workbook_Open()
    MsgBox "Welcome to Excel Application", vbInformation + vbOKOnly, "Workbook_Open()"
End Sub

The MsgBox() Function needs at least the first parameter value i.e. the Message Text, second and third parameter values are optional.

Second Parameter value is a combination of three different values, but any of them or all of them can be omitted in MsgBox() function:

• Specifies the type of Message box icon type, like information, question, help and so on.
• Message Box Buttons, like OK button only or OK and Cancel buttons or Abort, Ignore and Retry buttons etc.
• Third item specifies which button is selected as default, if more than one button is specified.

Example for second parameter:

MsgBox "Logical Errors in Program",vbAbortRetryIgnore+vbCritical+vbDefaultButton2,"Main()"

The highlighted values can be in any order joined with + sign.  The highlighted text is actually VBA defined Integer Constant values and they are added up together and the result value is used for displaying the message box Icon and Buttons.

Image of the Message Box displayed with the above statement is given below:

• vbAbortRetryIgnore - specifies the message box should have Abort, Retry and Ignore Buttons and it is defined as a VBA constant like Public Const vbAbortRetryIgnore As Integer = 2.
• vbCritical - specifies the message box icon. vbCritical = 16.
• vbDefaultButton2 - says select the second option (Retry button) as default. vbDefaultButton2 = 256.
• vbAbortRetryIgnore+vbCritical+vbDefaultButton2 = 2+16+256.

The above values can be specified in any order and is optional.  If you omit the second parameter the OK Button will appear in the Message Box.

Third parameter is the Title Text (appears on the top) of the message box and is optional.  Normally it carries the name of the Function or Sub-Routine, if specified.  If the message box is used for reporting logical errors the title can give the programmer a clue as where to look for debugging the code.

Before trying out the above Open Event Procedure we will add one more message box to display a message before closing the active workbook.

1. While the Workbook object is in selected state in the left side control select BeforeClode from the right side drop down list.
2. Modify the new procedure with the following lines of Code or copy and paste it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim txtMsg As String
Dim buttons As Integer
txtMsg = "Shutting Down Excel Application, Bye."
buttons = vbInformation
MsgBox txtMsg, buttons, "BeforeClose()"
End Sub

1. Save the Workbook in Macro Enabled Format (with .xlsm extension) and close it.  If your workbook is not in macro enabled format (Excel2007) then Excel will prompt you to save the workbook in a Macro enabled format (with .xlsm extension). If you don't select this format you will loose the program you have written in your Wokbook.
2. Open the Workbook with our programs.  You will see the Welcome message from the first program, immediately after opening the Workbook.
3. Now, close the Workbook.  Before closing the Workbook the BeforeClose() program runs and displays the “Shutting Down Excel Application, Bye.” message.

• Displaying worksheet Formula
• Microsoft Excel Automation
• Microsoft Excel Automation-2