In this tutorial, we would learn about a very important functionality provided by VBA. We will learn about Message Box and its Syntax along with some useful examples. So let us start learning.
Message Box – MsgBox Function
A Message box is a dialog box that prints some message on the screen. We can also choose the button to display on the message box so that the user could give respond to the message shown. We can also add an icon on the Message Box.
The MsgBox function is used to show a Message Box using VBA on the screen. The MsgBox function returns an Integer based on what button is clicked by the user to respond to the message.
The input parameters required by the MsgBox function are as follows.
- prompt – This is the required parameter that asks for the actual message that we want to print on the message box. It is a text string whose length can be 1024 characters long.
- [button] – This is an optional parameter and it has two parts. These parts when specified are separated using +.
- button – We specify the button for the message box. We use predefined VBA constants like vbOKCancel to show an Ok and Cancel button.
- icon – This tells the icon we want to show along with the message. For example, vbExclamation shows an Exclamation icon.
- [title] – This is the title of the message box at the title bar.
- [helpfile] – It is also an optional parameter, which identifies the help file to use.
- [context] – An optional argument, that identifies the Help context number assigned by the Help author to the appropriate Help topic. If the context is provided, helpfile must also be provided.
Different Examples of Message Box
In this section of the blog, we would perform some of the practical examples in VBA to use Message boxes to print a message on the screen.
Also Read: InputBox function VBA – Syntax and Examples
Example 1 – A Welcome Message using VBA
We can print a Welcome Message with the help of a Message Box.
Sub Welcome() MsgBox ("Welcome!") End Sub
We specify the prompt parameter as “Welcome!”. When you run this Sub Procedure, this message is shown on the screen.
You see that the title of this message box is “Microsoft Excel”. This is so because when we do not pass the Title argument of the MsgBox Function, it displays the application name by default.
Example 2 – A Message to Show Total Number of Sheets in the Workbook
The Sheets Collection contains all the sheets of the excel workbook. We can use the Count method of Sheets Collection Object to get the total count of sheets in the workbook. Use the & to concatenate the text string along with the sheets count in the Prompt argument of MsgBox Function.
Sub Sheet_Count() 'use & to concatenate, x contains integer returned by MsgBox function x = MsgBox("Total Number of Sheets are:= " & Sheets.Count, vbInformation) End Sub
When you run this sub procedure, a message box pops up displaying the total number of worksheets. Here an information icon is displayed because of the button argument.
The message box function returns 1 in this case because we specified the button argument. x stores the number returned by Msgbox corresponding to the Ok button.
This is the list of values returned by MsgBox function when the corresponding button is clicked by the user.
- 1 – vbOK – OK was clicked
- 2 – vbCancel – Cancel was clicked
- 3 – vbAbort – Abort was clicked
- 4 – vbRetry – Retry was clicked
- 5 – vbIgnore – Ignore was clicked
- 6 – vbYes – Yes was clicked
- 7 – vbNo – No was clicked
Example 3 – Message Box to Show the App Installation Message
We can display a message box using VBA that has the message for application installation unsuccessful message.
Sub App_Installed() x = MsgBox("MyFirstApp was not installed", vbCritical + vbAbortRetryIgnore,"MyFirstApp") End Sub
Run this sub procedure, it would show the message box for application installation unsuccessful message along with the buttons Abort, Retry and Cancel. It also has a critical icon. We specify the name of the application at the title bar to be “MyFirstApp”. The user can click on any of the three buttons.
We can also take actions based on the button clicked by the user in the Message Box using VBA. As we would know that x contains the integer value corresponding to the button clicked but the user. We can use the value with conditional structures to get different functionality based on user action.
Sub App_Installed() x = MsgBox("MyFirstApp was not installed", vbCritical + vbAbortRetryIgnore, "MyFirstApp") If x = 3 Then MsgBox ("Aborted") ElseIf x = 4 Then MsgBox ("Retrying") Else MsgBox ("Ignored") End If End Sub
If the user clicks on the Retry button, it displays another message box for “Retrying”. The same for other buttons.
There are the following values of button argument of the Msgbox Function.
- 0 vbOKOnly – Displays OK button only.
- 1 vbOKCancel – Displays OK and Cancel buttons.
- 2 vbAbortRetryIgnore – Displays Abort, Retry, and Ignore buttons.
- 3 vbYesNoCancel – Displays Yes, No, and Cancel buttons.
- 4 vbYesNo – Displays Yes and No buttons.
- 5 vbRetryCancel – Displays Retry and Cancel buttons.
- 16 vbCritical – Displays Critical Message icon.
- 32 vbQuestion – Displays Warning Query icon.
- 48 vbExclamation – Displays Warning Message icon.
- 64 vbInformation – Displays Information Message icon.
- 0 vbDefaultButton1 – First button is default.
- 256 vbDefaultButton2 – Second button is default.
- 512 vbDefaultButton3 – Third button is default.
- 768 vbDefaultButton4 – Fourth button is default.
- 0 vbApplicationModal Application modal – The current application will not work until the user responds to the message box.
- 4096 vbSystemModal System modal – All applications will not work until the user responds to the message box.