Excels MsgBox Function
In this article, we'll discuss the MsgBox function in much more detail and show you how you can use the different options to customize your Visual Basic Applications.
The MsgBox Function
First, let's take a look at the way we've been using the MsgBox function.
In this example, a simple message box displays with the text "Hello." An OK button is displayed for the user to acknowledge and close the message.
Even though we've only used a part of the MsgBox function, you can use it with a few more parameters. Let's first take a look at the MsgBox syntax for reference.
MsgBox (Text_String [, buttons] [, title] [, helpfile, context])
In the syntax above, any parameters in brackets are optional. Notice that "Text_String" is the only parameter requirement that is not optional. We've been using the MsgBox function with the basic parameter requirements.
The "Text_String" parameter is the message that you want to show to your users.
The "buttons" option lets you define the buttons you want to display. We've just been using the default OK button, but the MsgBox function lets you display an OK, Cancel, Retry, Abort and several combinations of these buttons. The buttons you show depends on the logic you want to have in your code.
The "title" option is a string that determines the message shown in the title bar. Many developers leave this empty, but you can customize your macros to show a title that describes the message.
The "helpfile" option is rarely used, but it's a way to specify the application's help file you want to use should the user click the help button. This option works with the "context" option. The context option defines the number in the help file that should be displayed if the user clicks the help button. Both of these parameters are used together. For instance, you can't define a context number but no helpfile option.
Let's take a look at some examples of the MsgBox function.
Open the Visual Studio Editor in Excel. If you recall, you can open it by pressing the ALT+F11 key combination, or you can click the Visual Basic button in the Developer tab. Double-click a module already created fs. If you don't have the macro anymore, you can right-click the project explorer window and choose to create a new module file. Give the module file any name you want.
Within the module, copy and paste the following code into the editor.
Sub MyCustomMessage ()
MsgBox "This is a test message.", "This is the test message title"
In the above example code, we create a subroutine named MyCustomMessage. This subroutine has only one line of code. It's our MsgBox code. Notice that we have another string added to the function parameters. It's separated by a comma. When you pass parameters to a function, you separate each parameter with a comma. String values are always contained within quotes, so don't forget the quotes with your string parameters. Notice that the comma is outside of the quotes. The comma separator is always placed after the quote character in a string parameter value.
Run this code using the Run button in the VBE toolbar. Now, when you see the message appear in the editor, notice that the message box now has a title. When you leave this option blank, you see no title in the title bar.
You can also add a pre-made icon next to your message. If you've ever noticed other application message boxes, they usually have an icon that specifies what type of message is displayed. A white icon with an "i" in the center indications that the message is just for the user's information. The red icon with an x in it indicates that it's an error or important alert. A yellow icon with an exclamation point is a warning message.
Let's take a look at a message box with a yellow warning icon.
Sub MyCustomMessage ()
MsgBox "This is a test message.", vbExclamation, "This is the test message title"
Notice that we place parameters in the right order as defined by the MsgBox description we covered previously. You must put parameters in the right order, or the MsgBox function will give you an error.
We used the same subroutine named MyCustomMessage. We then used the MsgBox function again, but you'll notice that there is a third parameter set as the second parameter. The vbExclamation parameter tells the MsgBox function to display a message with the yellow warning exclamation icon. Notice that the vbExclamation parameter doesn't have quotes, which indicates that it's not a string variable. This variable actually maps to an integer value. Variables with the "vb" prefix indicate that it's a system pre-made variable that has its own value already assigned to it. You don't place variables in quotes, so this parameter does not use quotation marks. When the MsgBox function runs, it automatically translates the value to the appropriate integer.
vbCritical Displays a Critical Message icon.
vbQuestion Displays a Question icon.
vbExclamation Displays a Warning Message icon.
vbInformation Displays an Information Message icon.
What if you have a long message that you need to format with carriage returns? The Visual Basic language has a pre-defined variable for this as well. The following code shows you how to add a carriage return and line feed to the message text.
Sub MyCustomMessage ()
MsgBox "This is a test message 1." & vbCrLf & "This is a test message 2.",, "This is the test message title"
In the above example code, we've added an ampersand character between a pre-defined variable named vbCrLf. The ampersand symbol is used to concatenate Visual Basic strings. Since we want to use a pre-defined character to separate two strings, we must terminate the first string, add the carriage return and line feed variable, and then concatenate it with the second string. We didn't add any other option parameters except the title of the window. When you view the above message, it will be formatted like the below.
This is a test message 1.
This is a test message 2.
If we left the string variable without the vbCrLf variable, the message box would display the message in the following format.
This is a test message 1. This is a test message 2.
When you use the vbCrLf variable in VB, just remember that it stands for "carriage return and line feed," which adds a carriage return to the text displayed to your users.
Retrieving Input from a MsgBox
The MsgBox function returns data back to the program if alternative buttons are added. If you recall from the previous section, we mentioned that you can add other buttons such as Abort, Retry or Ignore. Let's take a look at some sample code.
Sub MyCustomMessage ()
result = MsgBox("A critical error occurred. Try again?", vbAbortRetryIgnore + vbCritical, "Error Found")
In the above example, we've removed the default OK button from the popup message box and used the Abort, Retry and Ignore buttons. Since these buttons have different meaning than just OK, we need to assign the results to a variable. In this example, we've assigned the button value to the variable named result.
Each button clicked returns a different value. The following is a list of return values for each Visual Basic button.
1 - vbOK
2 - vbCancel
3 - vbAbort
4 - vbRetry
5 - vbIgnore
6 - vbYes
7 - vbNo
You use these return values in a Switch or If command in your code to then control the logic flow. The button values are used to determine what your code does when a user clicks any of the buttons. For instance, if the user clicks the Abort button, you probably want to stop the program. If the user clicks the Retry button, you want to attempt to run a function again without any errors.
You'll use the MsgBox function often in your programming. Even if you just want to test certain variables and see their values, you can use a MsgBox to view the content of a variable. When you use the MsgBox function, just remember that code execution is suspended until the user chooses an option by clicking a button. This option is then stored in a variable in the form of an integer.
- Working with the Internet with VBA
- How to Manage Excel Ranges Using Visual Basics
- How to Use VBA to Create Pivot Tables and Charts
- Getting Started with Visual Basics and Excel
- Editing the Excel Ribbon Using VBA
- How to Use the What-if Analysis Advanced Features in Excel 2019
- How to Start Using VBA in Excel 2019
- Ways to Manipulate Text in Excel 2019
- Using the What-If Analysis in Excel 2019
- Using HTML5 to Add Styles and Classes to Your Web Pages
- Using INNER and OUTER JOINs in SQL
- The Most Useful And Overlooked Functions in Excel 2019
- Database Design in MySQL
- Tables, Databases, and SQL...oh my!
- About The Latest Versions of HTML5 and CSS