In this guide, we’re going to show you how to show a closing message in Excel by using macros.
ThisWorkbook object and Workbook_BeforeClose Event
If you want to trigger any action at the before closing of your workbook, you must add your code under Workbook_BeforeClose. The event must be added into the ThisWorkbook object of your workbook.
You can open the ThisWorkbook object by simply double-clicking on its name in the Project Explorer pane of the Visual Basic for Applications (VBA) window.
The code to show a closing message
You have two options to show a closing message: Either use a simple message box (MsgBox) or create your own form and call the form. The message box method is relatively easier to do compared to the form: All you need is a single line of code with your message.
MsgBox "Thank you for using our workbook"
This line pops up a message box with an OK button.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Thank you for using our workbook"
End Sub
Let's continue with a few customization options that you can apply to closing message boxes.
Multiple lines
By default, a message box displays your message in a single line. If you want to see more lines in your message box, add the vbNewLine value into the text.
MsgBox "Thank you for using our workbook" & vbNewLine & "SpreadsheetWeb.com"
Dynamic values
You can also concatenate your closing text with a value in your workbook to add a dynamic value. For example, the following example demonstrates displaying values in cell B4, along with the message itself.
MsgBox "Thank you for using our workbook. " & [B4]
Modifying message icon and title
The default message box doesn't contain any formatting or icons, and comes with the "Microsoft Excel" title. Each behavior can be altered by supplying the necessary arguments in the code.
The second argument of the MsgBox command determines what the message box includes, like buttons or icons. There are four (4) constant values for icons:
| Constant | Description |
| vbCritical | Shows the critical message icon |
| vbQuestion | Shows the question icon |
| vbExclamation | Shows the warning message icon |
| vbInformation | Shows the information icon |
The following is an example for using an information icon.
MsgBox "Thank you for using our workbook.", vbInformation
The next argument is for the title. Enter a string value to alter the title of the message box.
MsgBox "Thank you for using our workbook.", vbInformation, "Good Bye"
If you want to change the title only, omit the second argument. Note that the repeating commas (, ,) indicate the second argument.
MsgBox "Thank you for using our workbook.", , "Good Bye"
Save your workbook to show a closing message
Our last tip for showing a closing message is to save your workbook as a Macro-enabled Excel Workbook (XLSM). Otherwise, your code will not be saved.
