The VBA MsgBox Function

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.

  Table 12-2: CONSTANTS USED FOR BUTTONS IN THE MSGBOX FUNCTION
  Open table as spreadsheet

  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 :

Table 12-3: CONSTANTS USED FOR MSGBOX RETURN VALUE
Open table as spreadsheet

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.

Figure 12-5: The buttons argument of the MsgBox function determines which buttons appear.

 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.

Figure 12-6: Splitting a message into multiple lines.

 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.

Figure 12-7: This message box displays text with tabs and line breaks.

 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.