Display An Pop Up Message To Users Opening An Excel File - Macro Monday

Welcome, Excellers and time for another #macromonday. Today I want to share with you how to display we welcome (or any other message) on your Excel workbook when it is opened. I use this small VBA macro to inform users on the status of some selected Excel reports. For example, I have a few reports to that consolidate data from other Excel worksheets. These are not all updated at once and the end user needs to be kept updated as to the % of the report that is completed. As I update the Excel workbook I simply update the % of the report that is completed. When I have completed the consolidation then I simply change the message to either 100% or something like “Report Completed”.

Let’s start writing our simple macro.

What Does The Macro Do?

This macro will display a message of your choice upon the user opening the Excel workbook.

How Does The Macro Work?

We use the Workbook_Open Event to trigger the message box using the message box function to display the message you want to to the users.

The syntax of MsgBox function in VBA

The syntax of VBA Msgbox is as follows:

MsgBox (Text_String [, buttons] [, title] [, helpfile, context])

where

‘Text_String’ is the message that you want the msgbox to display. The maximum length of ‘Text_String’ is 1024 characters.

‘buttons’ parameter specifies the type of buttons and icon that you want to be shown on the message box. It is an optional parameter. If you omit it then msgbox shows the default ‘vbOKOnly’ button.

‘title’ refers to the text displayed in the title bar of the message box. This is an optional parameter.

‘helpfile’ is a string parameter that specifies the help file to be used for the dialogue box. It is also an optional parameter but it becomes mandatory if ‘context’ parameter is to be used.

‘context’ is a numeric parameter that specifies the number assigned to the appropriate Help topic. It is an optional parameter but it becomes mandatory if ‘helpfile’ parameter is used.

Let’s Write The Excel Macro.

Step 1. Open the Visual Basic Editor. You can do this by either selecting the Developer Tab | Code | Visual Basic. Alternatively, you can hit ALT+F11 as a shortcut to open the Visual Basic Editor.

Step 2. Double click on This Workbook in the Project Explorer. The code is specific to this specific Exel workbook, therefore, the code needs to be stored within the workbook itself.

Step 3. Add the code below to the Workbook_Event.

Step 4. Save, Close and then re-open the file to test your code.

When I have updated and progressed completion of the Excel file, I simply change the message displayed to the user. This is my message below to the user when I have done all updates to the file and it is ready for general use.