VBA's MsgBox function is an easy way to display a message to the user or to get a simple response (such as OK or Cancel). I use the MsgBox function in many of this book's examples as a way to display a variable's value.
The official syntax for MsgBox is as follows :
MsgBox(prompt[,buttons][,title][,helpfile, context])
prompt : Required. The text displayed in the message box.
buttons : Optional. A numeric expression that determines which buttons and icon are displayed in the message box. See Table 12-2.
Constant | Value | Description |
---|---|---|
vbOKOnly |
| Display OK button only. |
vbOKCancel | 1 | Display OK and Cancel buttons. |
vbAbortRetryIgnore | 2 | Display Abort, Retry, and Ignore buttons. |
vbYesNoCancel | 3 | Display Yes, No, and Cancel buttons. |
vbYesNo | 4 | Display Yes and No buttons. |
vbRetryCancel | 5 | Display Retry and Cancel buttons. |
vbCritical | 16 | Display Critical Message icon. |
vbQuestion | 32 | Display Warning Query icon. |
vbExclamation | 48 | Display Warning Message icon. |
vbInformation | 64 | Display Information Message icon. |
vbDefaultButton1 |
| First button is default. |
vbDefaultButton2 | 256 | Second button is default. |
vbDefaultButton3 | 512 | Third button is default. |
vbDefaultButton4 | 768 | Fourth button is default. |
vbSystemModal | 4096 | All applications are suspended until the user responds to the message box (might not work under all conditions). |
vbMsgBoxHelpButton | 16384 | Display a Help button. However, there is no way to display any help if the button is clicked. |
title : Optional. The caption in the message box window.
helpfile , context : Optional. The helpfile and help topic.
You can easily customize your message boxes because of the flexibility of the buttons argument. (Table 12-2 lists the many constants that you can use for this argument.) You can specify which buttons to display, whether an icon appears, and which button is the default.
You can use the MsgBox function by itself (to simply display a message) or assign its result to a variable. When MsgBox does return a result, it represents the button clicked by the user. The following example displays a message and an OK button and does not return a result:
Sub MsgBoxDemo() MsgBox "Macro finished with no errors." End Sub
To get a response from a message box, you can assign the results of the MsgBox function to a variable. In the following code, I use some built-in constants (described in Table 12-3) to make it easier to work with the values returned by MsgBox :
Constant | Value | Button Clicked |
---|---|---|
vbOK | 1 | OK |
vbCancel | 2 | Cancel |
vbAbort | 3 | Abort |
vbRetry | 4 | Retry |
vbIgnore | 5 | Ignore |
vbYes | 6 | Yes |
vbNo | 7 | No |
Sub GetAnswer() Dim Ans As Integer Ans = MsgBox("Continue?", vbYesNo) Select Case Ans Case vbYes ' ...[code if Ans is Yes]... Case vbNo ' ...[code if Ans is No]... End Select End Sub
The variable returned by the MsgBox function is an Integer data type. Actually, it's not even necessary to use a variable to utilize the result of a message box. The following procedure is another way of coding the GetAnswer procedure.
Sub GetAnswer2() If MsgBox("Continue?", vbYesNo) = vbYes Then ' ...[code if Ans is Yes]... Else ' ...[code if Ans is No]... End If End Sub
The following function example uses a combination of constants to display a message box with a Yes button, a No button, and a question mark icon; the second button is designated as the default button (see Figure 12-5). For simplicity, I assigned these constants to the Config variable.
Private Function ContinueProcedure() As Boolean Dim Config As Integer Dim Ans As Integer Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox("An error occurred. Continue?", Config) If Ans = vbYes Then ContinueProcedure = True _ Else ContinueProcedure = False End Function
The ContinueProcedure function can be called from another procedure. For example, the following statement calls the ContinueProcedure function (which displays the message box). If the function returns False (that is, the user selects No), the procedure ends. Otherwise, the next statement would be executed.
If Not ContinueProcedure() Then Exit Sub
The width of the message box depends on your video resolution. If you would like to force a line break in the message, use the vbCrLf (or vbNewLine ) constant in the text. The following example displays the message in three lines. Figure 12-6 shows how it looks.
Sub MultiLine() Dim Msg As String Msg = "This is the first line." & vbCrLf & vbCrLf Msg = Msg & "This is the second line." & vbCrLf Msg = Msg & "And this is the last line." MsgBox Msg End Sub
You can also insert a tab character by using the vbTab constant. The following procedure uses a message box to display the values in a 20 — 8 range of cells in A1:H20 (see Figure 12-7). It separates the columns by using a vbTab constant and inserts a new line by using the vbCrLf constant. The MsgBox function accepts a maximum string length of 1,023 characters , which will limit the number of cells that you can display.
Sub ShowRange() Dim Msg As String Dim r As Integer, c As Integer Msg = "" For r = 1 To 20 For c = 1 To 8 Msg = Msg & Cells(r, c) & vbTab Next c Msg = Msg & vbCrLf Next r MsgBox Msg End Sub
CROSS-REFERENCE | Chapter 15 includes a UserForm example that emulates the MsgBox function. |